Today shortly on how to master the Excel MsgBox. Although, the MessageBox is a common control used in most Window applications many VBA developers underestimate its usage – often making custom UserForms for tasks where the Excel MsgBox could just as well do the trick. An example? User Interaction – when user input is needed many VBA devs create custom UserForms not knowing that the MsgBox can return a Yes/No, Retry/Cancel, Ok/Cancel response when configured properly.

Example Excel MsgBox question

This example will produce a simple Excel MsgBox question with Yes/No buttons:

If MsgBox("Do you agree?", vbYesNo, "Question") = vbYes Then
    'If Yes...put some code here
Else
    'If No...do this
End If

Easy right? Yet this is just a sample of what you can do with the MsgBox. See the link above to learn more on how to efficiently utilize the Excel MsgBox in your VBA projects!

Common uses for the Excel MsgBox

The Excel MsgBox can be used in multiple different ways. See some valuable mentions below:

• Notification – show the user a pop-up with an information or notification message e.g. code has finished executing
• Error handling – show the user an error pop-up message informing that a critical error has occurred. You can add a Retry button to ask if the user wants to retry running the code
• Question – show the user a pop-up with a simple question on how to proceed with running the code e.g. do you want to open a file?, do you want to close the workbook?

MsgBox: Messages, Notifications, Questions

The common VBA message box can be well used to interact with users to show messages (information, errors, notifications etc.) or just as well ask simple questions (Ok/Cancel, Retry/Cancel). Let us start by analyzing the parameters of the MsgBox function.

Function: MsgBox
Parameters:

• Prompt – the message you want to show in the message box
• Buttons – Optional. One of the following set of buttons below. You can sum them up to get different kinds of message boxes like this vbRetryCancel + vbCritical :
  • vbOKCancel – Display OK and Cancel buttons
  • vbAbortRetryIgnore – Display Abort, Retry, and Ignore buttons
  • vbYesNoCancel – Display Yes, No, and Cancel buttons
  • vbYesNo – Display Yes and No buttons
  • vbRetryCancel – Display Retry and Cancel buttons
  • vbCritical – Display Critical Message icon
  • vbQuestion – Display Warning Query icon
  • vbExclamation – Display Warning Message icon
  • vbInformation – Display Information Message icon
  • vbDefaultButton1 – First button is default
  • vbDefaultButton2 – Second button is default
  • vbDefaultButton3 – Third button is default
  • vbDefaultButton4 – Fourth button is default
  • vbApplicationModal – Application modal; the user must respond to the message box before continuing work in the current application
  • vbSystemModal – System modal; all applications are suspended until the user responds to the message box
  • vbMsgBoxHelpButton – Adds Help button to the message box
  • vbMsgBoxSetForeground – Specifies the message box window as the foreground window
  • vbMsgBoxRight – Text is right aligned
  • vbMsgBoxRtlReading – Specifies text should appear as right-to-left reading on Hebrew and Arabic systems
• Title – Optional. The title of the Message Box
• Help – Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
• Context – Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

Now some examples:

Retry with Critical Error Icon

Call MsgBox("Try to run the VBA code again?", vbRetryCancel + vbCritical, "Error")

Align message and Question Icon

Call MsgBox("Align this to the right hand side", vbQuestion + vbMsgBoxRight, "Some Message")

User interaction with the MsgBox
The message box is a great tool for asking simple Yes/No questions within your VBA code. See some examples below:

If MsgBox("Do you agree?", vbYesNo, "Question") = vbYes Then
    'If Yes...put some code here
Else
    'If No...then do this
End If

This will show the following question and pause the code until the user clicks on one of the buttons:

Similarly you can create a retry question which is convenient for error handling:

  On Error Goto ErrorHandl:
Retry:
  'Some code here..
ErrorHandl:
  If MsgBox("Retry?", vbYesNo, "Question") = vbRetry Then
     Goto Retry  
  End If

Let me know what you think!