How to use the MsgBox in Excel VBA

    

In this blog post, you will learn about MsgBox in Excel VBA Step by Step.

The VBA MsgBox is a dialog box in which you can inform the users about your program.

For example, see the below image:-

Image-01

Use the below VBA code for the above msgbox image:

Sub Basic_Message_Box() MsgBox "I am the Message Box" End Sub

The above message box is the 'Default' type message box but we can create different types of message boxes with different button options, you will learn further in this blog post.

Important Note:-

1.    Always use the doubt quote (“ ”), when using text string in the message box.

2.    Do not use the double quote(“ ”) when entering number values

3.    Use (#) when entering a date_value in the message box

Using numeric values in the message box

See the below message box for numeric values

\Image-02

See below VBA code for the above message box.

Sub Message_Box_With_Number_Value() MsgBox 20000 End Sub

Entering Dates in the Message box

Entering date values in the message box.

Image -03

See the below VBA code for entering date values in the message box

Sub Message_Box_With_Date_Value() MsgBox #3/1/2021# End Sub

Here have entered (#) value in the VBA code window but it is not appearing in the message box.

These are the basic use or forms of VBA message box.

We can create different types of message boxes in VBA.

Message box has some parameters, which are as follows:

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

prompt -     Required, displayed as the message in the dialog box.  The maximum length  of prompt is approximately 1024 characters, depending on the width of the characters used.

buttons -     [Optional]. That is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If omitted, the default value for the button is zero ("0").

title      -       [Optional], string expression displayed in the title bar of the dialog box. If you omit the title, the application name is placed in the title bar.

helpfile    -   [Optional]. A string expression that identifies the Help file to use to provide context-sensitive help for the dialog box. If a helpfile is provided, the 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, a help file must be provided.

Note:  Only the prompt argument is mandatory and rest square brackets arguments are optional. 

Button parameter covers different types of values, below table containing all 

types of button that we can use.

We can use these numeric values for making or creating different types of button

option, in the below list, see the numeric values which are appearing just before 

the button option.

See the list as follows:

No.         VBA Code                              Button Description

0            vbOKOnly                          - Displays OK button only.

1            vbOKCancel                       - Displays OK and Cancel buttons

2            vbAbortRetryIgnore           - Displays Abort, Retry, and Ignore buttons.

3            vbYesNoCancel                  -  Displays Yes, No, and Cancel buttons

4            vbYesNo                             - Displays Yes and No buttons

5            vbRetryCancel                   - Displays Retry and Cancel buttons

16          vbCritical                         - Displays Critical Message icon

32          vbQuestion                      - Displays Warning Query icon.

48          vbExclamation               - Displays Warning Message icon.

64          vbInformation                 - Displays Information Message icon

and more  ...

Now we will use the above number values to create different types of button in 

the message box, one by one.

MsgBox -  vbOkOnly (0) button (Default)

This is the default message box type with only one parameter ‘prompt’ without any optional arguments.

See the default message box image below:

See below, the VBA code for the above message box:

Sub Basic_Message_Box() MsgBox "I am the Message Box" End Sub

MsgBox - vbOkCancel (1) – Ok and Cancel Buttons

Below message box containing “Ok” and “Cancel” buttons with a prompt message.

See the code below for the above message box:

Sub Message_Box_Ok_Cancel() MsgBox "Want to enter the value?", vbOKCancel End Sub

MsgBox - vbAbortRetryIgnore (2) - Abort, Retry, and Ignore Buttons

See below message box for above button option, such as Abort, Retry and

Ignore with prompt parameter.

See the VBA code below for the above box:

Sub Message_Box_Abort_Retry_Ignore() MsgBox "Want to enter the value?", vbAbortRetryIgnore End Sub

MsgBox – vbYesNoCancel(3) - Yes, No, and Cancel Buttons

This VBA code option will display buttons like Yes, No, and Cancel with ‘Prompt’

Parameter text.

See the message box image below for the above options:

See the VBA code below for the above image:

Sub Message_Box_Yes_No_Cancel() MsgBox "Want to enter the value?", vbYesNoCancel End Sub

MsgBox – vbYesNo (4) - Yes and No Buttons

The below image displays only two-button “Yes” and “No”.

See below, the VBA code for the above image:

Sub Message_Box_Yes_No() MsgBox "Want to enter the value?", vbYesNo 'you can use '4' in place of "vbYesNo End Sub

MsgBox – vbRetryCancel (5) - Displays Retry and Cancel              Buttons

The above option displays the below message box, with ‘Retry’ and ‘Cancel’ 

button options.

See below, the VBA code for the above message box:

Sub Message_Retry_Cancel() MsgBox "Want to enter the value?", vbRetryCancel 'you can use '5' in place of "vbRetryCancel End Sub

MsgBox – vbCritical (16) - Displays Critical Message icon

Below the message box is displaying a ‘Critical’ message icon with ‘prompt’ text message:

And see below the VBA code for the above image:

Sub Message_Critical() MsgBox "Want to enter the value?", vbCritical 'you also can use '16' in place of "vbCritical End Sub

MsgBox – vbQuestion(32) - Displays Warning Query icon

The above message box code will display the ‘Warning Query’ sign, see in the

image below.

And see below the VBA code for the above image:

Sub Message_Question() MsgBox "Want to enter the value?", vbQuestion 'you can use '32' in place of "vbQuestion End Sub

MsgBox – vbExclamation (48) - Displays Warning Message icon

The above message box button option will display the ‘Exclamation’ sign with 

the ‘Prompt’ text parameter. We can replace “vbExclamation with a number 

value  (48) for the same result.

And see the VBA code for the above image:

Sub Message_Exclamation() MsgBox "Want to enter the value?", vbExclamation 'you can use '48' in place of "vbExclamation" End Sub

 

MsgBox – vbInformation (64) - Displays Information Message icon

The above message box option will display an ‘Information’ sign with the 

‘Prompt’ text parameter:

And see the VBA code for the above message box.

Sub Message_Information() MsgBox "Want to enter the value?", vbInformation 'you can use '64' in place of "vbInformation" End Sub

Here we have explained a message box with its multiple options with example.

I hope you find this tutorial useful.

Please feel free to put your comment or suggestion in the comment box below.

Thanks 

Narendra

Related Post

How to Change or Rename the Sheet Name in Excel VBA

What is Current Region Property in VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Change the Background Color in Excel VBA

How to Change Font Color in Excel VBA