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:
Argument | Required/Optional | Explanation |
---|---|---|
Prompt | Required | A statement will be shown in the message box. |
[Buttons] | Optional | Button and icon codes to display buttons and icons in the message box. |
[Title] | Optional | Title or name of the message box. |
[HelpFile] | Optional | Index or link which is assigned to the Help button in the message box. |
[Context] | Optional | Index or specific topic number of the Help file. |
- 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
Code Breakdown
- Here, we declared a sub procedure named Simple_Msgbox.
- Then, we declared sheet_name and title1 as string. Next, we inserted a string value as title1 and assigned the value of the active worksheet name to sheet_name using the name property.
- The MsgBox is displayed using the MsgBox function, with the message prompt specified as the value of sheet_name. We also specified the Title as variable title1.
- Now, click on the run button to see the MsgBox.
- Here, you can see that “MsgBox” is displayed as it is the name of our active worksheet. Using this code you can see the name of your own active worksheet in the MsgBox. By default, it will display an OK
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
Code Breakdown
- Firstly, we declared each_ID as a range.
- Then, we set the worksheet named “Multiple Corresponding Values” as Sheet.
- Next, we created an InputBox to take any ID No as Input and assigned the value as Insert_ID.
- Now, we searched for the input value in Column B using the Find
- After that, we used the If-Else statement to return Student ID not Found! If the input value is not present in the range or else return the corresponding value of the ID using the Offset
- Finally, run the code and insert the ID No you have been looking for. Here, we inserted 1102. So, it returns the corresponding values of that ID No in Msgbox.
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
Code Breakdown
- Here, we declared cell as range and name and title1 as string.
- Then, we set the cell as the first cell of the range as Cell C5 and let a string as title1.
- Next, we used a Do Unit loop until the cell is empty using the IsEmpty
- While running in the loop, each name will be added as a name keeping the previous value and adding a new line.
- We used the offset property so that the loop can continue to the next row.
- Lastly, we used the MsgBox function to show all the values that are stored as name
- Finally, press the run button and you will get all the names present in the worksheet in the MsgBox.
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
Code Breakdown
- Firstly, we declared text as string and assigned the value as “Do you want to add new values?” and msg and variable as Integer.
- Next, we assigned the value return value of a MsgBox as msg variable where we set the text variable as prompt and used the Yes No button. When the user clicks Yes the value of msg is 6 and when No its value is 7. To know details about the buttons go through the section we have provided below in the article.
- Then, we used an If else statement to return “Please enter your data.” in a MsgBox if the value of msg is 6 or else return “Proceed next.”
- Finally, click on the run button to display the first Msgbox. If you click on the Yes button another Msgbox will pop up with the prompt “Please enter your value.” and if you click on the No button it will show “Proceed next.”
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:
Button Codes | Values | Description |
---|---|---|
vbOKOnly | 0 | Shows the Ok button only (Default). |
vbOKCancel | 1 | Shows OK and Cancel buttons. |
vbAbortRetryIgnore | 2 | Shows Abort, Retry and Ignore buttons. |
vbYesNo | 3 | Shows Yes and No buttons. |
vbYesNoCancel | 4 | Shows Yes, No and Cancel buttons. |
vbRetryCancel | 5 | Shows Retry and Cancel buttons. |
vbMsgBoxHelpButton | 16384 | Shows Help Button. |
vbDefaultButton1 | 0 | Defines the first button default. |
vbDefaultButton2 | 256 | Defines the second button default. |
vbDefaultButton3 | 512 | Defines the third button default. |
vbDefaultButton4 | 768 | Defines the fourth button default. |
vbMsgBoxRight | 524288 | The alignment of the text is right. |
vbMsgBoxRtlReading | 1048576 | Text reading from right to left like Arabic and Hebrew languages. |
- 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:
Icon Codes | Values | Description |
---|---|---|
vbCritical | 16 | Displays the critical message icon- Thin white cross inside a red-filled circle. |
vbQuestion | 32 | Displays the question message icon- White question mark inside a blue-filled circle. |
vbExclamation | 48 | Displays the warning message icon- Black exclamatory symbol inside a yellow-filled triangle. |
vbInformation | 64 | Displays information message icon- The letter ‘i’ in white inside a blue-filled circle. |
- 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!