Office tutorials

VBA MsgBox window in Excel

VBA MsgBox – data presentation in window

You need to know the VBA MsgBox window while writing your VBA first program. The window of this function is one of many possibilities of data presentation using VBA code in Excel. It can be used to display any message, the result of your program etc. Below is an example of displaying the MsgBox VBA window with a simple message:

Excel VBA course - Excel VBA MsgBox window

Excel VBA course – Excel VBA MsgBox window

 

How to display the MsgBox message when opening an Excel file?

The VBA MsgBox window has many other parameters besides displaying the message. You can for example, set the window title, its size, etc. If you would like the window to be used to display a message when opening an Excel file, you can do it in the following way. Open the VisualBasic editor in the Developer tab. In the left Project tab of the editor choose MicrosoftExcelObject, then select ThisWorkbook In the upper left list. Next select the “Workbook” object in objects and in the right “Open” as a procedure. You can enter the following message in your editor, which will be displayed each time you open your Excel file: 

Remember that everything works correctly, the Excel file is saved in the format xlsm (Excel workbook with macro support). You also need to have macro support enabled in your Excel program.

Excel VBA course - MsgBox window

Excel VBA course – MsgBox window

You can also initially define a variable that will be responsible for the content of the message and then use it in your message:

 

MsgBox window – function arguments

As I said, the MsgBox window in Excel has a number of additional options. The full model of the VBA MsgBox function looks like this:

You can program such arguments as:

Prompt – this is the only required argument. You enter here the text or values ​​that you want to display in your message.

Buttons – the argument is not required. You can define in it the window style and the type of buttons that will be displayed in the message. A table with all possible buttons is listed below. If you do not specify this argument, only the OK button will be displayed. If you do not define a style, it will be automatically vbOKOnly, and the OK button itself.

Title – the argument is not required. In the Title argument, you define the title of the MsgBox window.

HelpFile – the argument is not required. The argument specifies the reference to the help file. You enter the help file number in the Context argument.

Context – the argument is not required. In this argument, yourefer to the help file using numerical value specified in the previous HelpFile.

If you do not want to use some arguments, all we need to do is separate them with commas, for example:

 

Below is a description of all options for the buttons argument :

Constant Description
vbAbortRetryIgnore Displays the Abort, Retry and Ignore buttons
vbApplicationModal Displays the modal window. The message pauses the current application until you click OK
vbCritical Displays a message in the form of a fatal error icon and an OK button
vbDefaultButton1 Displays the first button as the default
vbDefaultButton2 Displays the second button as the default
vbDefaultButton3 Displays the third button as the default
vbDefaultButton4 Displays the fourth button as default
vbExclamation Displays the message in the form of a warning icon and the OK button
vbInformation Displays a message in the form of an information icon and the OK button
vbMsgBoxHelpButton Displays the OK and Help buttons
vbMsgBoxRight Displays a message from right to left
vbMsgBoxRtlReading Displays a window in the form from right to left
VbMsgBoxSetForeground Displays the message in the foreground
vbOKCancel Displays the OK and Cancel buttons
vbOKOnly Displays only the OK button. The button is displayed from the default. Ie. If you do not define it, this style will be displayed
vbQuestion Displays the OK button and the question mark icon
vbRetryCancel Displays the Retry and Cancel buttons
vbSystemModal Displays the modal window. The message stops execution of all applications until you click OK
vbYesNo Displays the Yes and No. buttons
vbYesNoCancel Displays Yes, No and Cancel buttons

 

How to use the result returned by the MsgBox window buttons?

Depending on which buttons you will use in your applications, your program may work differently if you press the OK, Cancel, Yes, No, etc. buttons. When you press the button in the MsgBox window, you get a respond that you can use in your application. The constant values ​​returned after pressing the MsgBox window buttons are listed below:

Constant Numerical value Description
vbOK 1 The OK button was pressed
vbCancel 2 The Cancel button was pressed
vbAbort 3 The Cancel button was pressed
vbRetry 4 The Retry button was pressed
vbIgnore 5 You have clicked the Ignore button
vbYes 6 Yes was pressed
vbNo 7 Button vbNo was pressed

 

Examples of using the MsgBox window

You already know the VBA MsgBox window. Let’s create some simple programs using the most important window functionalities.

Example 1:

To begin, display the MsgBox VBA window displaying the message “Welcome User”

Example 2:

Then create a window with the same message and give it the title “My program window”

 

Excel VBA course - VBA MsgBox. Message and title

Excel VBA course – VBA MsgBox. Message and title

 

Example 3:

Let’s create a window informing about the occurrence of a fatal error,  with a critical error icon. The message that should appear is “Program error occurred”. The window title is “Critical error”

Excel VBA course - MsgBox VBA. Critical Error message

Excel VBA course – MsgBox VBA. Critical Error message

Example 4:

Importantly, in the second argument of the MsgBox function, youcan enter more options than one. For example, you can report a critical error, yes / no questions etc. If you want to do this, you need to combine styles with the + sign. Below is an example in which you can insert the buttons Yes / No / Cancel and the message with the question icon in the same window.

The value named DefaultButton2 informs you, that the second button is selected by default, in this case No-message. You need also fill in the title and content of the message with the values ​​”Are you an experienced programmer?” and “Answer the question”. In addition, each value in the message should be defined as variables:

Excel VBA course - VBA MsgBox yes no cancel questions

Excel VBA course – VBA MsgBox yes no cancel questions

Example 5:

Below, let’s modify our example from task 4. After clicking on the buttons by the user Yes or No put respond messages about the contents of “Congratulations !!!”, or “Keep on working !!!”. You have to use the conditional instruction If Then Else, which I am writing about in next articles.

Let’s use the return from the first message named vbYes and vbNo. You do not anticipate any action for the Cancel button, so it will simply close your window.

Excel VBA course - VBA MsgBox yes no cancel questions and respond

Excel VBA course – VBA MsgBox yes no cancel questions and respond

I present all examples in this chapter in the file below:

vba_msgbox_examples.xlsm

 

Excercises (You can enter the solution in a comment)

1. Write a program using a MsgBox window with title, message with any content, buttons Yes and No. The No-button should not be set as the Default button. Please display the message itself as a window with the question icon.

2. Modify example listed above Set the canceled message after click on the Cancel button.

 

More examples of Excel VBA basics can be found in the Excel VBA Tutorial section and  Excel VBA Examples section. If you have any questions about this chapter, you can post them on the forum without logging in.

Leave a Reply

Your email address will not be published.