VBA MsgBox – Complete Guide

VBA MsgBox - Complete Guide with code examples

The MsgBox displays a message with direct interaction from the user. The Msgbox is window that appears on the screen that prompts the user to make a selection, or display information.

Let’s begin by understanding the syntax.

MsgBox in VBA

 

MsgBox

Syntax:

MsgBox(prompt[,buttons][,title][,helpfile,context])

  • Prompt − (Required) – A String that is displayed as a message in the dialog box. The maximum length is approximately 1024 characters.
  • Buttons − (Optional) A Numeric expression that specifies the type of buttons to display. Refer to the table below.
  • Title − (Optional) A String expression displayed in the title bar of the dialog box. If the title is left blank, the application name is placed in the title bar.
  • Helpfile − (Optional) A String expression that identifies the Help file to use for providing context-sensitive help for the dialog box.
  • Context − (Optional) A Numeric expression that identifies the Help context number assigned by the Help author to the appropriate Help topic. If context is provided, helpfile must also be provided.

 

Sub YesNoDemo()
' Dim statement is used to Assign the variables
Dim Message, Style, Title, Help, Ctxt, Response, MyString
Message = "Do you like to code?" ' Msgbox Message
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Buttons to be displayed
Title = "Excelbuddy.com MsgBox Demonstration" ' Msgbox Title
Help = "helpfile.txt" ' Define Help file.
Ctxt = 3000 ' Define topic context.

‘ Displaying the actual MsgBox variable inputs from above
Response = MsgBox(Message, Style, Title, Help, Ctxt)
If Response = vbYes Then ‘ Yes as selected answer
MyString = “Yes” ‘ You can add a performed action
Else ‘ No as selected answer
MyString = “No” ‘ You can add a performed action
End If

End Sub

Do you like to code?

VBA MsgBox Button Parameters

The button parameters are broken up into 5 groups. These constants work as follows:

  1. Group 1 – Buttons
  2. Group 2 – Icons
  3. Group 3 –  Select which button is highlighted when the message box appears.
  4. Group 4 – Set the modal type of the message box.
  5. Group 5 – General settings

Please note: When using the Msgbox function, you can only select one of each category. This is shown in the code demonstrations below.

  1. button type
  2. icon type
  3. default button
  4. modal type
Constant Group Type Description
vbOKOnly 1 Buttons Ok button.
vbOKCancel 1 Buttons Ok and cancel buttons.
vbAbortRetryIgnore 1 Buttons Abort, Retry and Ignore buttons.
vbYesNoCancel 1 Buttons Yes, No and Cancel buttons.
vbYesNo 1 Buttons Yes and No buttons.
vbRetryCancel 1 Buttons Retry and Cancel buttons.
vbCritical 2 Icon Critical Message icon.
vbQuestion 2 Icon Warning Query icon.
vbExclamation 2 Icon Warning Message icon.
vbInformation 2 Icon Information Message icon.
vbDefaultButton1 3 Default button Set button 1 to be selected.
vbDefaultButton2 3 Default button Set button 2 to be selected.
vbDefaultButton3 3 Default button Set button 3 to be selected.
vbDefaultButton4 3 Default button Set button 4 to be selected. Note that there will only be four buttons if the help button is included with vbAbortRetryIgnore or vbYesNoCancel.
vbApplicationModal 4 Modal Cannot access Excel while the button is displayed. Msgbox is only displayed when Excel is the active application.
vbSystemModal 4 Modal Same as vbApplicationModal but the message box is displayed in front of all applications.
vbMsgBoxHelpButton 5 Other Adds a help button
vbMsgBoxSetForeground 5 Other Sets the message box windows to be the foreground window
vbMsgBoxRight 5 Other Right aligns the text.
vbMsgBoxRtlReading 5 Other Specifies text should appear as right-to-left reading on Hebrew and Arabic systems.

VBA MsgBox Return Values

The following are all the return values for the MsgBox function:

vbOk
vbCancel
vbAbort
vbRetry
vbIgnore
vbYes
vbNo

Each of these values represents a button that was clicked.

Msgbox Example

 

The following code will prompt the user with a statement with “Yes” or “No” as inputs.

In the code below, we will be using the constant vbYesNo from the above chart. We define the input as vbYes. If the the user selects Yes, then the message “Coffee runs the world!” is displayed. If they don’t select Yes, ELSE, the alternative message “How do you operate without coffee?” is displayed.
 

 

Sub YesNoDemo()

' Here we display the Return Values
' Make sure the values questions are in parenthesis

If MsgBox("Do you like coffee? ", vbYesNo) = vbYes Then
MsgBox "Coffee runs the world!"
Else
MsgBox "How do you operate without coffee?"
End If

End Sub

Msgbox Message Boxes

 

VBA Do you like Coffee?

Group 2 – Msgbox Icon Demos

 

Here are examples how to use Group 2 of the Msg Button Paramenters. Group 2 is the Icon’s displayed in the pop-up window. There are four to choose from.

  ' Yes/No buttons with Critical icon
 resp = MsgBox("Having fun yet?", vbYesNo + vbCritical)
 
 ' Yes/No buttons with Warning Query icon
 resp = MsgBox("Having fun yet?", vbYesNo + vbQuestion)
 
 ' Yes/No buttons with Warning Message icon
 resp = MsgBox("Having fun yet?", vbYesNo + vbExclamation)

 ' Yes/No button with Information Message icon
 resp = MsgBox("Having fun yet?", vbYesNo + vbInformation)

 

Here are the display messages in Excel.

 

VBA Warning

vbCritical

vbQuestion

VBA Exclaimation

vbExclamation

VBA Information

vbInformation

Additional Examples using Button Parameters

 

 

  ' This shows how 4 buttons: Yes, No, Cancel, Help plus it highlights button 2
 resp = MsgBox("Example of Button 2 Highlighted", vbYesNoCancel + vbDefaultButton2 + vbMsgBoxHelpButton)
 

 

Button 2 highlight

 

 

  ' This shows how 4 buttons: Abort, Retry, Ignore, Help plus it highlights button 3
 resp = MsgBox("Example of Button 2 Highlighted", vbAbortRetryIgnore + vbDefaultButton3 + vbMsgBoxHelpButton)
 

 

abort retry ignore

Leave a Reply

Pivot Tables

Overview: Pivot tables are one of the most versatile and powerful tools available in Excel. They allow you to display

Read More »

DSUM Function

The Dsum function is used to calculate a specified column in a database that correlates with a specified criteria. =DSUM

Read More »
Scroll to Top