[fusion_text]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 – 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 – 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

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

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

[/fusion_text][title size=”1″ content_align=”left” style_type=”none” sep_color=”” margin_top=”10px” margin_bottom=”” class=”” id=””]Group 1: Types of Buttons displayed on Message Box[/title][fusion_text]

[/fusion_text][title size=”1″ content_align=”left” style_type=”none” sep_color=”” margin_top=”10px” margin_bottom=”” class=”” id=””]Group 2: Icon styles displayed on the message box[/title][fusion_text]

[/fusion_text][title size=”1″ content_align=”left” style_type=”none” sep_color=”” margin_top=”10px” margin_bottom=”” class=”” id=””]Group 3: Which button to set as default[/title][fusion_text]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.

[/fusion_text][title size=”1″ content_align=”left” style_type=”none” sep_color=”” margin_top=”10px” margin_bottom=”” class=”” id=””]Group 4: Overall design or modal of the message box[/title][fusion_text]

As you can see, All the parameters are categorized in 4 groups.[/fusion_text][title size=”1″ content_align=”left” style_type=”none” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]Rule to combine these parameters[/title][fusion_text]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.[/fusion_text][title size=”1″ content_align=”left” style_type=”default” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]Values returned by MsgBox Function on Pressing each Button[/title][fusion_text]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[/fusion_text][fusion_text]

[/fusion_text][fusion_text]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.[/fusion_text][title size=”1″ content_align=”left” style_type=”none” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]Examples : Few most frequently used Message Box Types[/title][fusion_text]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.[/fusion_text][title size=”1″ content_align=”left” style_type=”none” sep_color=”” margin_top=”20px” margin_bottom=”” class=”” id=””]Message box with Yes/No and execute macro only if it is pressed Yes or No[/title][fusion_text]

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

[/fusion_text][title size=”1″ content_align=”left” style_type=”none” sep_color=”” margin_top=”20px” margin_bottom=”” class=”” id=””]Execute specific macro when pressed Cancel button from Message Box[/title][fusion_text]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

[/fusion_text][fusion_text]

[/fusion_text]