Excel VBA MsgBox – Ultimate Tutorial

Msgbox allows you to communicate information to your user or request guidance on the next action that you’d like them to take.

MsgBox can take up to 5 arguments, the message being the only one that’s mandatory.

Bear in mind that the message box is disruptive, meaning it halts processing until the user takes action such as pressing return or clicking OK.

MsgBox has two syntax types, statement and function, use the statement type when you just want to communicate a message to your user, use the function syntax when you need the user to choose from some options such as "OK" and "Cancel" or "Yes" and "No".

MsgBox Statement

01  ' Very simple Messagebox with one argument
02  MsgBox "Finished Processing"
03
04  ' Simple User Message with three arguments
05  MsgBox "Finished Processing", vbInformation, "App Title"

When viewing the code above, line 2 results in the MsgBox on the left below and Line 5 creates the more customised MsgBox on the right.

Note that when only adding the message as an argument you get the default MsgBox with "Microsoft Excel" as its title without any custom icons to the left of the message.

MsgBox Function

Note that line 2 below uses the functional form of MsgBox returning a value to lResult (data type long).

01  ' Very simple Messagebox with one argument
02  lResult = MsgBox("Message Goes Here", vbOKCancel + vbDefaultButton1, "Title e.g. BusinessProgrammer.com")
03
04  If lResult = vbOK Then
05      ' Note vbInformation used here
06      MsgBox "OK Clicked", vbInformation, "Result Time"
07  Else
08      ' Note vbExclamation used here
09      MsgBox MsgBox "Cancel Clicked", vbExclamation, "Result Time"
10  End IF

The code above on line two will give you the MsgBox on the lower right, note that in addition to vbOKCancel I’ve added vbDefaultButton1 this means that when the user presses Enter or Return the OK button is automatically pressed, if I’d used vbDefaultButton2 Cancel would be pressed.

The second arguments to MsbBox are the built in VBA constants that have the following numerical values.

• vbDefaultButton10
• vbOKCancel1
• vbOK1
• vbCancel2
• vbExclamation48
• vbInformation64

Line 4 above has lResult = vbOK which is the same as lResult = 1 however it’s best practice to use the built-in constants as it makes the code more readable and futureproof.

vbDefaultButton1 is the default but note vbDefaultButton1 to 4 exist depending on your requirements.

MessageBox Arguments and Options

MsgBox can take up to five arguments… (1) The message (2) Constants to customise the MsgBox (3) MsgBox Title (3) and (4) are related to help files that are beyond the scope of this article.

Arguments are covered in detail below …

• The message via hardcoded text or a variable

• A built in constant (dispayed below) these constants can be combined to give effects such as "vbQuestion + vbYesNo" to give a Yes No MsgBox with a question mark.

  When using multiple button options (vbYesNo etc) you need to treat the MsgBox as a function rather than a subroutine (i.e. line of code), meaning

  ConstantValueDescription

  vbOKOnly 0 Default, Ok Button Only”

  Copy following code line to VBA immediate window and press enter.

  MsgBox “Message Goes Here”, vbOKOnly, “Title e.g. BusinessProgrammer.com”

  

  As this only contains an OK button, there’s no point in using function syntax

  ConstantValueDescription

  vbOKCancel 1 Display, Ok and Cancel buttons

  Copy following code line to VBA immediate window and press enter.

  lResult = MsgBox("Message Goes Here", vbOKCancel, "Title e.g. BusinessProgrammer.com")

  

  lResult is data type long.

  This is used to capture the return value from the MsgBox.

  For example, you can use an IF statement to test if lResult equals vbOK or vbCancel and code appropriately.

  ConstantValueDescription

  vbAbortRetryIgnore2Displays three buttons Abort Retry and Ignore

  Copy following code line to VBA immediate window and press enter.

  lResult = MsgBox(“Message Goes Here”, vbAbortRetryIgnore, “Title e.g. BusinessProgrammer.com”)

  

  lResult is data type long.

  This is used to capture the return value from the MsgBox.

  For example, you can use an IF statement to test whether lResult equals vbAbort, vbRetry or vbIgnore and code appropriately.

  ConstantValueDescription

  vbYesNoCancel3Displays three buttons Yes, No and Cancel

  Copy following code line to VBA immediate window and press enter.

  lResult = MsgBox(“Message Goes Here”, vbYesNoCancel, “Title e.g. BusinessProgrammer.com”)

  

  lResult is data type long.

  This is used to capture the return value from the MsgBox.

  For example, you can use an IF statement to test whether lResult equals vbYes, vbNo or vbCancel and code appropriately.

  ConstantValueDescription

  vbYesNo4Displays two buttons Yes and No

  Copy following code line to VBA immediate window and press enter.

  lResult = MsgBox(“Message Goes Here”, vbYesNo, “Title e.g. BusinessProgrammer.com”)

  

  lResult is data type long.

  This is used to capture the return value from the MsgBox. For example, you can use an IF statement to test whether lResult equals vbYes or vbNo and code appropriately.

  ConstantValueDescription

  vbRetryCancel5Displays two buttons Retry and Cancel

  Copy following code line to VBA immediate window and press enter.

  lResult = MsgBox(“Message Goes Here”, vbRetryCancel, “Title e.g. BusinessProgrammer.com”)

  

  lResult is data type long.

  This is used to capture the return value from the MsgBox. For example, you can use an IF statement to test whether lResult equals vbRetry or vbCancel and code appropriately.

  ConstantValueDescription

  vbCritical16Displays Red Critical X Image with single OK button

  Copy following code line to VBA immediate window and press enter.

  MsgBox “Message Goes Here” & vbCrLf & “Network Down, Closing App”, vbCritical, “Title e.g. BusinessProgrammer.com”

  

  Allows you to display that something bad’s happend. Here I used vbCrLf to add an extra line to the MsgBox message.

  Notice we’re not capturing a return value here, we’re just telling the user "What’s What" and then running our shut down code for example.

  ConstantValueDescription

  vbQuestion32Displays Question Mark and OK button

  Copy following code line to VBA immediate window and press enter.

  lResult = MsgBox(“Message Goes Here”, vbQuestion, “Title e.g. BusinessProgrammer.com”)

  

  In it’s standard form (displayed here), it’s a little bit useless. It’s asking a question but not giving you any way to answer it.

  The solution is to combine it with one of the other arguments that add extra prompt buttons to the MsgBox.

  See below…

  ConstantValueDescription

  vbQuestion + vbYesNo36Combine two arguments to get Question Mark and Yes and No button, other combinations work also.

  Copy following code line to VBA immediate window and press enter.

  lResult = MsgBox(“This could take a long time.” & vbCrLf & “Do you want to continue”, vbQuestion + vbYesNo, “Title e.g. BusinessProgrammer.com”)

  

  Here the code’s warning the user that this could take some time. The lResult variable will either hold the value vbYes or vbNo which can be tested with an IF statement or Select Case.

  I also make use of vbCrLf to add an extra line to the message.

  Note the integer value 36 above which is the sum of vbYesNo (4) and vbQuestion (32).

  ConstantValueDescription

  vbExclamation48Display exclamation mark on MsgBox

  Copy following code line to VBA immediate window and press enter.

  MsgBox “3 out of 97 rows not processed” & vbCrLf & “Non processed rows have been tagged.”, vbExclamation, “Title e.g. BusinessProgrammer.com”

  

  Exclamation, unlike a question doesn’t require a special action from the user, therefore its simplest form works (i.e. make a statement without capturing a return value).

  I also make use of vbCrLf to add an extra line to the message.

  ConstantValueDescription

  vbExclamation + vbYesNo52Display I for information on MsgBox

  Copy following code line to VBA immediate window and press enter.

  lResult = MsgBox(“3 out of 97 rows not processed” & vbCrLf & “Non processed rows have been tagged.” & vbCrLf & “Do you want to view tagged rows?”, vbExclamation + vbYesNo, “Title e.g. BusinessProgrammer.com”)

  

  Here we’ve modified the previous msgbox to ask a question, answer will be captured with lResultValue being either vbYes or vbNo.

  Given that we’re not capturing a return value the simple syntax (rather than function syntax) above works just fine.

  ConstantValueDescription

  vbInformation64Display I for information on MsgBox

  Copy following code line to VBA immediate window and press enter.

  MsgBox “Finished Processing.”, vbInformation, “Title e.g. BusinessProgrammer.com”

  

  Here we’re just giving the user some (hopefully) useful information so we shouldn’t need to modify the buttons with extra arguments.

  Given that we’re not capturing a return value the simple syntax (rather than function syntax) above works just fine.

  Just like everything else, you can if you wish add yes/No or Abort Cancel etc buttons.

  Other arguments that are used less often include…

  • vbDefaultButton1, Make the first button default, Value 0
  • vbDefaultButton2, Second button default, Value 256
  • vbDefaultButton3, Third button default, Value 512
  • vbDefaultButton4, Fourth button default, Value 768

• Third Argument is the title of the MsgBox

• Fourth and Fifth arguments are related to adding help files to the MsgBox, these are seldom used and outside the scope of this article.

Wanna grab me a coffee? – Then Click This Green Box

If you find this article or videos really helpful, then consider grabbing me a coffee, it takes a lot of work and expense to keep this site going, the more caffine I have the harder I can work.

And if you do grab me a coffee (just click anywhere on this green area), thanks for your support 😉