VBA MsgBox window in Excel

VBA MsgBox – data presentation in window

You have lerned basics of VBA MsgBox window while writing your first program here: VBA first program. This window is one of many possibilities to present data using VBA code in Excel. It can be use to display messages, the program results 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 optional parameters that you can use to display your message. For example you can set the window title, its size, etc. If you want to use this window to display a message when opening an Excel file, you can do it in the following way.

Open the VisualBasic editor in the Developer’s tab. In the left Project tab choose MicrosoftExcelObject, then select ThisWorkbook in the upper left list. Next select “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 any time when you open your Excel file: 

Remember that everything works correctly when the Excel file is saved using xlsm format (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 define a VBA variable that will keep the message text. You can use it in your message:

MsgBox window – function arguments

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

You can program such arguments as:

  • Prompt – this is required argument. You enter here the text, values or variable ​​that you want to display in your message.
  • Buttons – This argument is not required. You can define here window style and the type of buttons that will be displayed in the message window. Table with all possible buttons is listed in this chapter. If you will not specify this argument, only the OK button will be displayed. If you will not define a style, it will be automatically vbOKOnly.
  • Title – This argument is not required. In the Title argument, you can define the title of the MsgBox window.
  • HelpFile – This argument is not required. The argument specifies the reference to the help file. You enter the help file number in the Context argument.
  • Context – This 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 :

ConstantDescription
vbAbortRetryIgnoreDisplays the Abort, Retry and Ignore buttons
vbApplicationModalDisplays the modal window. The message pauses the current application until you click OK
vbCriticalDisplays a message in the form of a fatal error icon and an OK button
vbDefaultButton1Displays the first button as the default
vbDefaultButton2Displays the second button as the default
vbDefaultButton3Displays the third button as the default
vbDefaultButton4Displays the fourth button as default
vbExclamationDisplays the message in the form of a warning icon and the OK button
vbInformationDisplays a message in the form of an information icon and the OK button
vbMsgBoxHelpButtonDisplays the OK and Help buttons
vbMsgBoxRightDisplays a message from right to left
vbMsgBoxRtlReadingDisplays a window in the form from right to left
VbMsgBoxSetForegroundDisplays the message in the foreground
vbOKCancelDisplays the OK and Cancel buttons
vbOKOnlyDisplays only the OK button. The button is displayed from the default. Ie. If you do not define it, this style will be displayed
vbQuestionDisplays the OK button and the question mark icon
vbRetryCancelDisplays the Retry and Cancel buttons
vbSystemModalDisplays the modal window. The message stops execution of all applications until you click OK
vbYesNoDisplays the Yes and No. buttons
vbYesNoCancelDisplays 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:

ConstantNumerical valueDescription
vbOK1The OK button was pressed
vbCancel2The Cancel button was pressed
vbAbort3The Cancel button was pressed
vbRetry4The Retry button was pressed
vbIgnore5You have clicked the Ignore button
vbYes6Yes was pressed
vbNo7Button 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 comment

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

One thought on “VBA MsgBox window in Excel”

%d bloggers like this: