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 :
|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:
|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.
To begin, display the MsgBox VBA window displaying the message “Welcome User”
'OfficeInside.org Sub MsgBoxExample() MsgBox "Welcome user" End Sub
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
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
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
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:
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.