VBA MsgBox window in Excel

Last Updated on January 11, 2023 by token

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 something 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:

'OfficeInside.org
Sub MsgBoxExample ()
 MsgBox "This is simple message"
End Sub

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. Message will be displayed any time when you open your Excel file: 

'OfficeInside.Org
Sub Workbook_Open()
 MsgBox ("Welcome to the program, have a nice day")
End Sub

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.

You can also define a VBA variable that will keep the message string. You can use it in your message:

OfficeInside.org
Sub MsgBoxExample ()
 Dim strMessage as String
 strComunication = "Contents of the message"
 MsgBox strMessage
End Sub

MsgBox window – function arguments

As I said, the MsgBox window in Excel has additional parameters. The full model of the VBA MsgBox function is listed bellow:

'OfficeInside.Org
Function MsgBox (Prompt, [Buttons As VbMsgBoxStyle = vbOKOnly], [Title], [HelpFile], [Context]) As VbMsgBoxResult

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 buttons 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, you can refer to the help file using numerical value specified in the previous HelpFile.

If you don’t want to use some arguments, all you need to do is separate them with commas, for example:

'OfficeInside.Org
MsgBox "Contents",, "Window title"

Below is the 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 message with fatal error icon and an OK button
vbDefaultButton1	Displays first button as the default
vbDefaultButton2	Displays second button as the default
vbDefaultButton3	Displays third button as the default
vbDefaultButton4	Displays fourth button as default
vbExclamation	Displays message with warning icon and the OK button
vbInformation	Displays message with information icon and the OK button
vbMsgBoxHelpButton	Displays the OK and Help buttons
vbMsgBoxRight	Displays message from right to left
vbMsgBoxRtlReading	Displays window from right to left
VbMsgBoxSetForeground	Displays message in foreground
vbOKCancel	Displays OK and Cancel buttons
vbOKOnly	Displays only OK button. The button is displayed from the default. Ie. if you do not define it, this style will be displayed
vbQuestion	Displays OK button and question mark icon
vbRetryCancel	Displays Retry and Cancel buttons
vbSystemModal	Displays modal window. Message will stop execution of all applications until you click OK
vbYesNo	Displays Yes and No buttons
vbYesNoCancel	Displays Yes, No and Cancel buttons

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

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 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 No was pressed

Examples of using the respond from 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”

'OfficeInside.org
Sub MsgBoxExample()
 MsgBox "Welcome user"
End Sub

Example 2:

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

'OfficeInside.org
Sub MsgBoxExample1()
 MsgBox "Welcome User",, "My program window"
 'The message and window title 
End Sub

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”

'OfficeInside.org
Sub MsgBoxExample2 ()
 MsgBox "Program error occurred", vbCritical, "Critical error"
 'Inserted: Critical error message, window title
End Sub

Example 4:

In the second argument of the MsgBox function, you can 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.

Value named DefaultButton2 informs you, that the second button is selected by default, in this case No-button. 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 values in the message should be defined as variables:

'OfficeInside.org
Sub MsgBoxExample4 ()
Dim strPrompt As String
Dim strStyle As String
Dim strTitle As String

strPrompt = "Are you an experienced programmer?"
strStyle = vbQuestion + vbYesNoCancel + vbDefaultButton2
strTitle = "Answer the question"

MsgBox strPrompt, strStyle, strTitle
End Sub

Example 5:

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 need to use the conditional instruction If Then Else. You will read more about it in this VBA Course.

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

'OfficeInside.Org
Sub MsgBoxExample5 ()
Dim strPrompt As String
Dim strStyle As String
Dim strTitle As String
Dim StrResponse As String

strPrompt = "Are you an experienced programmer?"
strStyle = vbQuestion + vbYesNoCancel + vbDefaultButton2
strTitle = "Answer the question"

StrResponse = MsgBox (strPrompt, strStyle, strTitle)
'Inserted: Message with question mark icon, answers yes, no, cancel, defaultbutton and title

If StrResponse = vbYes Then
MsgBox "Congratulations !!!"
ElseIf StrResponse = vbNo Then
MsgBox "Keep practicing !!!"
End If

End Sub

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 as a window with the question icon.

2. Modify your example listed above. Set the cancel 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 write it in comments.