Excel VBA: Show Multiple Values with Multiple Variables in MsgBox 

Msgbox is an easy way to show users different customized messages and values of different variables. It is created using the VBA MsgBox function which has 5 arguments in total. Among them, a prompt argument is required and the message we want to show in a MsgBox is assigned as a prompt. For example, to show multiple variables in VBA MsgBox you have to put that variable as a prompt.

To show multiple variables in MsgBox you can declare your variables first and then assign values to them. You can give different variable names for each data or declare variables as range, then loop through that range and show them in MsgBox.

Here, you can see that we have shown 3 variables in a MsgBox. The variables are in different data types but can be shown in a single MsgBox by using ampersand(&) operator.

In this article, you will find 5 easy examples of how you can show multiple variables in VBA MsgBox. Go through them to have a clear concept of when and where you can try these methods.

Download Practice Workbook

You can download the workbook to practice yourself.

Introduction to VBA MsgBox Function

• Function Objective:

The MsgBox function is used to create a message box which is also known as a dialogue box.

• Syntax:

MsgBox(Prompt, [Button As VbMsgBoxStyle = vbOkOnly], [Title], [HelpFile], [Context]) As VbMsgBoxResult

• Arguments Explanation:

• Return Parameter:

The function returns a statement along with the defined buttons in the message box.

• Final Outcome:

Here, 1 is the default Title, 2 is the prompt, 3 is the default button and 4 is the sign to close the Msgbox.

How to Launch VBA Editor in Excel

If you do not have the Developer tab added to the Excel ribbon, then follow the steps given below to add it and open the Visual Basic Editor.

• Firstly, click on the File tab.

• Then, click on Options.

• After that, go to the Customize Ribbon tab >> select Developer form Main Tabs >> click on Add. Click on OK.

• Thus, the Developer tab will be added to your Excel Ribbon.
• Next, go to the Developer tab >> click on Visual Basic.

• Now, the Microsoft Visual Basic Editor will open.
• Lastly, to add a module, click on Insert >> select Module.

How to Create MsgBox Using VBA in Excel

We have created several worksheets showing different examples to show variables in MsgBox. In the first worksheet, we have a dataset containing the information of ID No, Name, Age, and Weight of some students. Using this dataset we will show you how you can create a basic MsgBox that shows the sheet name in MsgBox.

To create a basic MsgBox you must add a prompt. Here, we will use the ActiveSheet.name property to show the name of the active sheet and assign that value as a variable. Then, put that variable as a prompt in MsgBox Function. Similarly, you can show other properties of a worksheet such as an address of a range in MsgBox.

You can also add a Title argument to add titles to your Msgbox. It will help the user to know what the MsgBox is about.

• To do that, insert the following code into your module.

Sub Creating_MsgBox()
Dim title1 As String
Dim sheet_name As String
'Set active worksheet name as variable
title1 = "Which worksheet are you working on?"
sheet_name = ActiveSheet.name
MsgBox Prompt:=sheet_name, Title:=title1
End Sub

Read More: How to Show Range Address with MsgBox in Excel VBA

How to Display Values in VBA MsgBox Through Variable in Excel

You can create a MsgBox to show variables creating a loop using VBA like the example we have shown below.

Here, we have a dataset containing the Sales of January and February and Total Sales for these two months are also calculated.

Now, using a code we will assign the values of Sales in a variable and loop through that range and multiply each value by 2 and show each value in a MsgBox.

• To do that, insert the following code into your module.

Sub MsgBox_with_Variable()
Dim sales As Range
Dim cells As Range
Dim var As Integer
Set sales = Range("C5:C9")
var = 2
For Each cells In sales
     cells = cells * var
MsgBox cells
Next cells
End Sub

Here, we declared sales and cells as Range and var as Integer. Next, we set the cell range C5:C9 for sale variable and assigned 2 as var. Then, we used a For loop to iterate through each cells in the sales range. Within each iteration, the cells value will get doubled and shown in a MsgBox.

• Now, if you run the code, it will show each value of the variable one by one.

5 Easy Examples to Show Multiple Values Through Variables in VBA MsgBox

Here, we will show you 5 examples to show multiple variables in VBA MsgBox using ampersand(&), add operator (+), loops and different other ways.

1. Adding Ampersand Between Variables to Show Multiple Values in VBA MsgBox

You can show multiple variables using ampersand(&) between them in MsgBox. The variables can be in different data types. Ampersand is a simple and easy-to-use operator to concatenate data. Using this you can also choose the order in which you want to show your variables. However, when you want to show a lot of variables in Msgbox it can be hard to read using this method. You can also make mistakes while using ampersand. It can lead to errors in your code.

Here, we will show an example where we will show string and integer values as variables in MsgBox.

• Firstly, insert the following code into your module.

Sub Adding_Ampersand()
Dim name As String, ID As String
Dim Age As Integer
name = "Ron"
ID = 1101
Age = 12
MsgBox "Name: " & name & ";  Student ID:" _
& ID & ";  Age:" & Age
End Sub

In the above VBA code, we declared name as string and assigned the value as “Ron”, ID as 1101 which is also a string and Age as 12 an integer. Then, to show them in the MsgBox we added ampersand between them.

• Lastly, click on the run button to see the following MsgBox.
• Thus, you can show multiple variables of different data types using ampersand(&).

Read More: How to Use MsgBox and InputBox in VBA Excel

2. Using Addition Operator Between Variables to Show Only String Values in VBA Msgbox

You can also use the addition operator (+) between variables to show them in a VBA MsgBox. It is a very simple and straightforward method. It leads to fewer errors than using ampersand (&). However, you can only use this operator if the variables are string values. Addition operator will perform a mathematical add operation if you use this for integer or double data type and show the result in the Msgbox. In those cases, you can convert your integer value to string by using the CStr function.

• To use the addition operator, to show multiple variables (string) in VBA, insert the following code into your module.

Sub Add_Operator()
Dim info1 As String, info2 As String
info1 = "age"
info2 = "weight"
'using add operator (+)
MsgBox "Here you will find " + info1 _
+ " and " + info2 + " of some students."
End Sub

In the above code, we declared info1 and info2 as string values and assigned “age” as the value of info1 and “weight” as the value of info2. Next, we used the addition operator to show these variables in the MsgBox.

• Then, press the run button to see the following MsgBox.
• Thus, you can use the addition operator between string variables to show values in VBA Msgbox.

3. Creating VBA Msgbox with Multiple Variables in Multiple Lines

Now we will show you how to put multiple variables in multiple lines. To do that you need to use the vbNewLine property after the variable after which you want to create a line break. Creating line breaks between variables makes it easy to read the variables in MsgBox for users. But it can take up a lot of space and for large amounts of data, users may need to scroll up and down.

• In the beginning, insert the following code into your module.

Sub Variables_in_Multiple_Lines()
Dim name As String, ID As String
Dim Age As Integer, title1 As String
title1 = "Student info"
name = "Ron"
ID = 1101
Age = 12
MsgBox "Name: " & name & vbNewLine _
& "Student ID: " & ID & _
vbNewLine & "Age: " & Age, , title1
End Sub

Here, we declared variables similar to the previous examples. Then, to add new lines for each variable we used the vbNewLine property after each variable.

• After that, run your code to show multiple variables in multiple lines.

4. Returning Multiple Corresponding Values in MsgBox Based on User Input

In this example, we will take an ID No as input value using an InputBox. Then, check if that value is in our dataset and return the corresponding values in the MsgBox. To do that, we will set each ID No as a variable. Following the example, you can learn to find a value in our dataset using InputBox and then retrieve corresponding data for that specific value and show them all inside a MsgBox. By doing this you will find data easily without going through each data. As we are using the Find method in the code to look for the user input value, it will take less time than looping through each value and can be easily used for a huge dataset.

• Use the following code inside your module to use this code.

Sub Multiple_Corresponding_Variables()
Dim Insert_ID, each_ID As Range
Set Sheet = Worksheets("Multiple Corresponding Values")
Insert_ID = InputBox("Enter Student ID:", "Student Info")
If Insert_ID <> "" Then
Set each_ID = Sheet.Range("B:B").Find(Insert_ID)
If each_ID Is Nothing Then
     MsgBox "Student ID not found!", , "Student Info"
Else
     MsgBox "Information of Student ID: " & Insert_ID & vbNewLine _
     & "Name: " & each_ID.Offset(0, 1).Value & vbNewLine _
     & "Age: " & each_ID.Offset(0, 2).Value & vbNewLine _
     & "Weight: " & each_ID.Offset(0, 3).Value, vbInformation, "Student Info"
End If
End If
End Sub

5. Showing Multiple Variables in MsgBox Using Do Until Loop

In the fifth example, we will use a Do Until Loop to return multiple variables in MsgBox. This loop is used when you want to do the same task multiple times until your desired condition is fulfilled. It is not the most efficient method when the dataset is huge. It could possibly slow down the program.

Here, using this loop we will find all the names of the students whose information is present in the worksheet. This loop will work until it finds any empty cell in the given range. However, if you have any empty cells in your data, it may not be able to show all the values as the loop stops where it finds any empty cell.

• To use this code, copy it to your module.

Sub Do_Until_Loop()
Dim cell As Range
Dim name As String, title1 As String
Set cell = Range("C5")
title1 = "Student Info"
Do Until IsEmpty(cell)
     name = name & cell.Value & vbNewLine
     Set cell = cell.Offset(1, 0)
Loop
MsgBox "We have information about:" & vbNewLine & name, , title1
End Sub

How to Display Two Values from Different Cells Using VBA MsgBox in Excel

Suppose, you have some values in your dataset which you want to use as a variable and show them in a MsgBox. You can easily do this by defining a range and then using Range.Value property and then assigning that value to a variable you can show that variable in MsgBox.

Here, we will use the following code to show the Total Sales in January and February in a MagBox by assigning the values as a variable first.

• Insert the following code in your module to do that.

Sub Values_from_Different_Cells()
Dim sales As Range
Set sales = Range("C11:C12")
MsgBox "Total Sales in Jan: $" & sales.cells(1, 1).Value _
& vbNewLine & "Total Sales in February: $" & sales.cells(2, 1).Value
End Sub

In the above code, we declared sales as Range and the value of cell range C11:C12 was assigned to it. Then, we used the Range.Cells.Value property to show the value of the specific cell in MsgBox. As our range is sales we used sales.cells. Here, cells(1,1) define the first row and first column of the defined range and cells(2,1) define the second row and first column of the defined range.

Now, run the code to display the two values from different cells in MsgBox

How to Create MsgBox That Take User Input Value & Assigns Value to Variable in Excel

In this example, we will show you how you can create MsgBox that returns user input value & assigns value to variable in Excel. As user input value we will use the return value of a MsgBox and assign it as a variable. Then, for 2 different values of the variable, we will return another MsgBox showing 2 different messages.

• Insert the following code inside your module to show such MsgBox.

Sub User_Input_as_variable()
Dim text As String, msg As Integer
text = "Do you want to add new values?"
Dim variable As Integer
'Setting the return value of msgbox as variable
msg = MsgBox(Prompt:=text, _
Buttons:=vbYesNo + vbInformation + vbDefaultButton2 + vbSystemModal)
'Showing the value of button you selected in msgbox
If msg = 6 Then
     MsgBox "Please enter your data."
Else: MsgBox "Proceed next."
End If
End Sub

How to Create MsgBox with Buttons in Excel

We can add different types of buttons in our MsgBox and also set a default button to that. Different buttons have different button codes and return different values. Here, we have provided a list of these button constants. You can use them according to your needs.

• Button Constants:

• To create a Yes No button you can use the following code. To do that, copy the code into your module.

Sub Yes_No_Buttons()
Dim text As String
Dim Msgbox_Value As Integer
text = "Do you want to add new values in the dataset?"
Msgbox_Value = MsgBox(Prompt:=text, Buttons:=vbYesNo)
Select Case Msgbox_Value
     Case vbYes: MsgBox "Please enter your values."
     Case vbNo: MsgBox "Proceed next."
End Select
End Sub

Here, we declared text as String and assigned the value which we want to show as prompt in the MsgBox to it. Then, as Buttons we typed vbYesNo to create a Yes No button. Lastly, we used the Select Case statement to return two different values for each button.

• Now, if you run the code, you will get a MsgBox with Yes No buttons with the prompt. If you select the Yes button the MsgBox will show “Please enter your values.” otherwise it will return “Proceed next.”

• You can also set a default button for this Msgbox. To do that, use the following code in your module.

Sub First_Button_as_Default()
Dim text As String
Dim Default_Button As Integer
text = "Do you want to add new values in the dataset?"
Default_Button = MsgBox(Prompt:=text, Buttons:=vbYesNo + vbDefaultButton1)
If Default_Button = vbYes Then
     MsgBox "Please enter your values."
ElseIf Default_Button = vbNo Then
     MsgBox "Proceed next."
End If
End Sub

Here, we added vbDefaultButton1 with the vbYesNo constant to set the first button as default.

• Now, if you run the code the Yes button will be selected as default as it is the first button. Similarly, using the other default button codes you can set any button as default in MsgBox.

Read More: Excel VBA: Develop and Use a Yes No Message Box

How to Change Icon Style for MsgBox Using VBA in Excel

In VBA, you can customize the style of a MsgBox by using one of the available constants as the second argument of the “MsgBox” function. Here, we have provided a list of these icon constants. You can use them according to your needs.

• Icon Constants:

• To create a MsgBox with a Critical icon you can use the following code. To do that, copy the code into your module.

Sub Critical_Style()
text = "You cannot change any value in this dataset."
'set button as vbCritical to have critical icon in msgbox
MsgBox Prompt:=text, Buttons:=vbCritical
End Sub

Here, we set vbCritical as Buttons in the MsgBox function to have a Critical Icon in MsgBox.

• Now, if you run the code the Msgbox will have a Critical Icon. Similarly, using the other icon codes you can add other types of icons in the MsgBox.

How to Create Application & System Modal MsgBox Using VBA in Excel

There are two types of modal in VBA MsgBox. They are the Application modal and System modal Msgbox. In case of Application modal, the user must reply to the message box in order to use the application (Excel). On the other hand, for System modal, the user must reply to the message box in order to use all the applications in your OS. To set application modal Msgbox use vbApplicationModal as buttons and vbSystemModal for system modal MsgBox.

• To create a MsgBox as System modal you can use the following code. To do that, copy the code into your module.

Sub System_Modal()
Dim text As String
text = "Here you will find the information of some students."
'set vbSystemModal as button
MsgBox Prompt:=text, Buttons:=vbSystemModal
End Sub

In the above code, we used vbSystemModal as Buttons in the MsgBox function.

• Now, run the code to display the system modal VBA MsgBox.

Things to Remember

• You must add the variables as a prompt into your MsgBox.
• By default the title of any MsgBox is set as Microsoft Excel and the button as OK

Frequently Asked Questions

1. Can I customize the size of a MsgBox?
No, you can not change or customize the size of a MsgBox.

2. Can I display a MsgBox without any buttons?
No, VBA MsgBox always has at least one button and by default, it is set as an OK button.

3. Can I display a MsgBox that requires the user to enter text?
No, you can not use MsgBox for that purpose. However, you can use an InputBox to enter text by the user.

Conclusion

So, in this article, you will find 5 examples to show multiple variables in VBA MsgBox. Use any of these ways to accomplish the result in this regard. We have also discussed how you can create MsgBox with different buttons, styles and modals. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit the ExcelDemy website for many more articles like this. Thank you!