Excel VBA Tutorial – Different types of message box in excel macro

.

[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

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

[/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]

Button Style Name Button Style Numeric Value Description
vbCritical 16 Critical Icon displays besides the message prompt
vbQuestion 32 Displays a Question icon besides the message prompt
vbExclamation 48 Displays exclamation icon besides the message prompt
vbInformation 64 Displays Information icon besides the message prompt
vbYesNo 4 Displays only Yes and No button
vbRetryCancel 5 Displays two buttons – Retry and Cancel

[/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]

Button Style Name Button Style Numeric Value Description
vbOKOnly 0 Default one. Displayed only OK button on the message Box
vbOKCancel 1 Message Box with OK and Cancel  two buttons
vbAbortRetryIgnore 2 Displays three buttons – Abort, Retry and Ignore
vbYesNoCancel 3 Message Box with 3 buttons – Yes, No and Cancel

[/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.

Button Style Name Button Style Numeric Value Description
vbDefaultButton1 0 First Button is always default one
vbDefaultButton2 256 This makes second button as default on the message box
vbDefaultButton3 512 This makes third button as default on the message box
vbDefaultButton4 768 This makes fourth button as default on the message box

[/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]

Button Style Name Button Style Numeric Value Description
vbApplicationModel 0 Default model
vbSystemModel 4096 System Model
vbMsgBoxHelpButton 16384 To add a help button to your message box
vbMsgBoxSetForeground 65536 Message box window is displayed as foreground window
vbMsgBoxRight 524288 Message box prompt is right aligned
vbMsgBoxRtlReading 1048576 This is for text to be displayed from Right to left. Arabic language

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]

Button Name Returned Constant Returned Numeric Value
OK Button vbOK 1
Cancel Button vbCancel 2
Abort Button vbAbort 3
Retry Button vbRetry 4
Ignore Button vbIgnore 5
Yes Button vbYes 6
No Button vbNo 7

 

[/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]

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.

[/fusion_text]

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

2 Comments

  1. Irene Marrin

    Thank you, I have been seeking for info about this topic for ages and yours is the best I have located so far.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest