How to Use the MsgBox Function in Excel 2016 VBA - dummies

How to Use the MsgBox Function in Excel 2016 VBA

By John Walkenbach

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.”
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.

A simple message box.
A simple message box.

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
    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.

A simple message box, with two buttons.
A simple message box, with two buttons.

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]…
‘    ...[code if Yes is not clicked]…
  End If
End Sub