Dear friends,
You must have seen windows message boxes. As the names suggests, they are basically used as a popup to give some message to the user on screen.

Based on type of message you are sending, you can format your message box accordingly. With Type of Message, I mean, is it a warning? Is it an Info? Is it critical? etc. Based on these type of message, icon in message box changes – which you must have noticed in many applications.

Apart from these types which I have mentioned above, you might have noticed that, not all the message boxes has only OK button. There are message boxes, which has different buttons as well, like with Yes/No button, Retry/Cancel Button etc.

All these different types of message boxes are inbuilt in Excel. You do not have to design them. All you need to know is – the parameters used in order to display them accordingly.

Simple and most common message box in Excel using VBA

Usually while writing any macro or VBA code, you definately try to put a simple message box just to say.. Task completed at the end of your Macro. Something like below image

Simple Message Box

Simple Message Box – Excel Macro

Let see the VBA code syntax for displaying message box in Excel

MsgBox VBA Function Syntax

MsgBox [Prompt], [ButtonsType], [Title], [Help File Path], [Context]

Where:

Prompt :

This is the ONLY argument which is mandatory in this MsgBox Function. As the name suggests this is the Text which you want to display as message in the Message Box.

ButtonsType

This is an optional argument. VBA has predefined list of buttons for message box. For example, Yes/No button, Retry/Cancel button etc. I have provided the list of button types below in a table. This is a numeric expression.
“OKOnly” is the default value (when you do not specify anything).
Note: VBA, deos not allow you to Change the caption, color size etc of these buttons. They are all predefined.

Title

(Optional) Title of the message box, which you want to provide. Default Title for the message box is “Microsoft Excel”

Help File Path

(Optional) This is for prividing URL for the Help file, in case you want to have a Help button on the message box and once, user clicks on it, then you are taken to the help file.

Context

(Optional) This is related to the Help file you provide above. This is numeric number which is assigned to a particular section of the Help file.
Note: If this is provided, then Help file MUST be provided.

Example: VBA code to Display Message Box in Excel

Lets start with a simple message box – just by providing the mandatory argument value i.e. Prompt.


Function displaySimpleMessageBox()
    MsgBox "Task Completed"
End Function

Result:

After running the above code, here is what you see:

Simple Message Box

Simple Message Box – Excel Macro

Message Box with Help Button

Now let’s have a look at the message box with all the parameters filled in.


Sub sampleMessageBox()
    Dim promptMessage
    Dim btnStyle
    Dim titleOfTheMessageBox
    Dim helpFile
    Dim contextNumber
    
    promptMessage = "This the prompt message"
    btnStyle = vbCritical + vbMsgBoxHelpButton + vbRetryCancel
    titleOfTheMessageBox = "This is the title of the message Box"
    helpFile = "\\helpfile.chm"
    contextNumber = 1000 'Section number in the help file
    
    MsgBox Prompt:=promptMessage, _
    Buttons:=btnStyle, _
    Title:=titleOfTheMessageBox, _
    helpFile:=helpFile, _
    Context:=contextNumber
End Sub

Note:

In the example, above as you can see I have combined 3 different Button style to one Message Box. Yes, you can combine more than one style on a message box but there is a rule. You can not randomly add as many as you want. I have explained this rule later in this article.

After running the above VBA code, following message box will be displayed.

Message Box with All the Parameters filled

Message Box

Button Styles for Message Box

All these parameters are grouped under following 4 categories.

  • Group 1: Types of Buttons displayed on Message Box
  • Group 2: Icon styles displayed on the message box
  • Group 3: Which button to set as default
  • Group 4: Overall design or modal of the message box

Group 1: Types of Buttons displayed on Message Box

Button Style NameButton Style Numeric ValueDescription
vbCritical16Critical Icon displays besides the message prompt
vbQuestion32Displays a Question icon besides the message prompt
vbExclamation48Displays exclamation icon besides the message prompt
vbInformation64Displays Information icon besides the message prompt
vbYesNo4Displays only Yes and No button
vbRetryCancel5Displays two buttons – Retry and Cancel

Group 2: Icon styles displayed on the message box

Button Style NameButton Style Numeric ValueDescription
vbOKOnly0Default one. Displayed only OK button on the message Box
vbOKCancel1Message Box with OK and Cancel  two buttons
vbAbortRetryIgnore2Displays three buttons – Abort, Retry and Ignore
vbYesNoCancel3Message Box with 3 buttons – Yes, No and Cancel

Group 3: Which button to set as default

As you can see here, there are only 4 buttons which you can set as default. This is true because, at max in a message box, you can not add more than 4 buttons.

Button Style NameButton Style Numeric ValueDescription
vbDefaultButton10First Button is always default one
vbDefaultButton2256This makes second button as default on the message box
vbDefaultButton3512This makes third button as default on the message box
vbDefaultButton4768This makes fourth button as default on the message box

Group 4: Overall design or modal of the message box

Button Style NameButton Style Numeric ValueDescription
vbApplicationModel0Default model
vbSystemModel4096System Model
vbMsgBoxHelpButton16384To add a help button to your message box
vbMsgBoxSetForeground65536Message box window is displayed as foreground window
vbMsgBoxRight524288Message box prompt is right aligned
vbMsgBoxRtlReading1048576This is for text to be displayed from Right to left. Arabic language

As you can see, All the parameters are categorized in 4 groups.

Rule to combine these parameters

It is a very simple rule. You can use only ONE parameters from each group at max. This means, in total, you can combine 4 parameters in a message box.

Buttons like Ok, Cancel, Yes, No etc. should have conditional statements to execute.
For example, if a message box has a Prompt like “do you want to continue?” with Yes and No button on it.
We should be able to capture the action which use has taken and based on that you start execution of that particular section.

Therefore, msgBox funciton returns some value on pressing the buttons. Even for cancel button, you should write code, if you want to cancel the program.

Let’s have a look the values what are returned by pressing different buttons from message box.

Values returned by MsgBox Function on Pressing each Button

Since MsgBox works like a VBA function as well, each button of message box, returns a specific value when user presses it.

Here is the list of values returned by MsgBox function on pressing buttons

Button NameReturned ConstantReturned Numeric Value
OK ButtonvbOK1
Cancel ButtonvbCancel2
Abort ButtonvbAbort3
Retry ButtonvbRetry4
Ignore ButtonvbIgnore5
Yes ButtonvbYes6
No ButtonvbNo7

 

By capturing the returned value from MsgBox function, you can always execute Excel Macro conditionally. Example: If Pressed yes.. then do this.. If No then do this.

Below are the examples where I have used MsgBox as a function to capture the response and do different tasks based on the buttons pressed.

Examples : Few most frequently used Message Box Types

Below examples will give you an idea, how you can capture the response from the message box and execute specific piece of codes.
You can use values from the above table and using these examples you can control the flow of execution of your macro, however you want.

Message box with Yes/No and execute macro only if it is pressed Yes or No


Sub msgBoxWithYesNoWhenYesOrNoPressed()
    Dim promptMessage
    Dim btnStyle
    Dim titleOfTheMessageBox
    Dim resMsgBox
    
    promptMessage = "Are you sure you want to clear data from Entire Sheet?"
    btnStyle = vbCritical + vbYesNo
    titleOfTheMessageBox = "Decistion Box - Clear Data"
        
    resMsgBox = MsgBox(Prompt:=promptMessage, Buttons:=btnStyle, Title:=titleOfTheMessageBox)
    If resMsgBox = vbYes Then ' or resMsgBox = 6 -- refer the above table
        Debug.Print "User has pressed Yes... write all the statement which you want to be executed when pressess Yes"
    Else
        Debug.Print "User has pressed Yes... write all the statement which you want to be executed when pressess No"
    End If
End Sub

In the above example, since, there are two buttons only, Yes and No, therefore, it make sense to assume that, If user has not pressed Yes, then it would have pressed No. That is why I have put the code in Else condition.

but if you have more than two buttons, then like vbYes (6), you can also check the response with vbNo (7) and then execute the relevant macro.


Sub msgBoxWithYesNoWhenNoPressed()
    Dim promptMessage
    Dim btnStyle
    Dim titleOfTheMessageBox
    Dim resMsgBox
    
    promptMessage = "Are you sure you want to clear data from Entire Sheet?"
    btnStyle = vbCritical + vbYesNo
    titleOfTheMessageBox = "Decistion Box - Clear Data"
        
    resMsgBox = MsgBox(Prompt:=promptMessage, Buttons:=btnStyle, Title:=titleOfTheMessageBox)
    If resMsgBox = vbYes Then ' or resMsgBox = 6 -- refer the above table
        Debug.Print "User has pressed Yes... write all the statement which you want to be executed when pressess Yes"
        Exit Sub
    End If
    
    If resMsgBox = vbNo Then ' or resMsgBox = 7 -- refer the above table
        Debug.Print "User has pressed Yes... write all the statement which you want to be executed when pressess No"
    End If
End Sub

Execute specific macro when pressed Cancel button from Message Box

In the below example, you can see, I have created 3 if-else clause for Yes, No and Cancel button separately.

You can execute different macros on pressing different buttons.


Sub msgBoxWithYesNoWhenCancelPressed()
    Dim promptMessage
    Dim btnStyle
    Dim titleOfTheMessageBox
    Dim resMsgBox
    
    promptMessage = "Are you sure you want to clear data from Entire Sheet?"
    btnStyle = vbCritical + vbYesNoCancel
    titleOfTheMessageBox = "Decistion Box - Clear Data"
        
    resMsgBox = MsgBox(Prompt:=promptMessage, Buttons:=btnStyle, Title:=titleOfTheMessageBox)
    If resMsgBox = vbYes Then ' or resMsgBox = 6 -- refer the above table
        Debug.Print "User has pressed Yes... write all the statement which you want to be executed when pressess Yes"
        Exit Sub
    End If
    
    If resMsgBox = vbNo Then ' or resMsgBox = 7 -- refer the above table
        Debug.Print "User has pressed Yes... write all the statement which you want to be executed when pressess No"
        Exit Sub
    End If
    
    If resMsgBox = vbCancel Then ' or resMsgBox = 2 -- refer the above table
        Exit Sub ' to exit from the program immediately
    End If
End Sub

Conclusion:

Now you know enough about message box in Excel VBA. You can display really awesome message boxes with your own Title, Prompt and different buttons.

If you have any question, doubt or suggestion, put them in the comment below. I would to respond as soon as possible.

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!