How to Use the MsgBox Function in Excel 2016 VBA
You’re probably familiar with the VBA MsgBox function. The MsgBox function, which accepts the arguments shown below, is handy for displaying information and getting simple user input. It’s able to get user input because it’s a function. A function, as you probably know, returns a value. In the case of the MsgBox function, it uses a dialog box to get the value that it returns. Keep reading to see exactly how it works.
|Argument||What It Affects|
|Prompt||The text Excel displays in the message box|
|Buttons||A number that specifies which buttons (along with what icon)
appear in the message box (optional)
|Title||The text that appears in the message box’s title bar
Here’s a simplified version of the syntax of the MsgBox function:
MsgBox(prompt[, buttons][, title])
Displaying a simple message box
You can use the MsgBox function in two ways:
To simply show a message to the user: In this case, you don’t care about the result returned by the function.
To get a response from the user: In this case, you do care about the result returned by the function. The result depends on the button that the user clicks.
If you use the MsgBox function by itself, don’t include parentheses around the arguments. The following example simply displays a message and does not return a result. When the message is displayed, the code stops until the user clicks OK.
Sub MsgBoxDemo() MsgBox “Click OK to begin printing.” Sheets(“Results”).PrintOut End Sub
Look below to see how this message box looks. In this case, printing commences when the user clicks OK. Do you notice that there is no way to cancel the printing? Keep reading to find out how to fix that.
Getting a response from a message box
If you display a message box that has more than just an OK button, you’ll probably want to know which button the user clicks. You’re in luck. The MsgBox function can return a value that represents which button is clicked. You can assign the result of the MsgBox function to a variable.
In the following code, some built-in constants are used that make it easy to work with the values returned by MsgBox:
Sub GetAnswer() Dim Ans As Long Ans = MsgBox(“Start printing?”, vbYesNo) Select Case Ans Case vbYes ActiveSheet.PrintOut Case vbNo MsgBox “Printing canceled” End Select End Sub
|Constant||Value||What It Does|
|vbOKOnly||0||Displays OK button only.|
|vbOKCancel||1||Displays OK and Cancel buttons.|
|vbAbortRetryIgnore||2||Displays Abort, Retry, and Ignore buttons.|
|vbYesNoCancel||3||Displays Yes, No, and Cancel buttons.|
|vbYesNo||4||Displays Yes and No buttons.|
|vbRetryCancel||5||Displays Retry and Cancel buttons.|
|vbCritical||16||Displays Critical Message icon.|
|vbQuestion||32||Displays Warning Query icon.|
|vbExclamation||48||Displays Warning Message icon.|
|vbInformation||64||Displays Information Message icon.|
|vbDefaultButton1||0||First button is default.|
|vbDefaultButton2||256||Second button is default.|
|vbDefaultButton3||512||Third button is default.|
|vbDefaultButton4||768||Fourth button is default.|
Check out how it looks. When you execute this procedure, the Ans variable is assigned a value of either vbYes or vbNo, depending on which button the user clicks. The Select Case statement uses the Ans value to determine which action the code should perform.
You can also use the MsgBox function result without using a variable, as the following example demonstrates:
Sub GetAnswer2() If MsgBox(“Start printing?”, vbYesNo) = vbYes Then ‘ ...[code if Yes is clicked]… Else ‘ ...[code if Yes is not clicked]… End If End Sub