The Excel VBA MsgBox is a highly effective technique or tool for improving the usability and accessibility of Excel operations. Depending on the MsgBox you are using, MsgBox can display different sorts of outputs and let you know what is happening with your code. Additionally, you can provide information and select an option using MsgBox.
This Content Covers:
- What is an Excel VBA Message Box?
- Different Parts of an Excel VBA Message Box.
- Syntax and Purpose of Excel VBA Message Box.
- How to Insert a VBA Message Box and Create Prompt Text?
- Inserting Different Buttons with Codes.
- Ok and Cancel Buttons
- Yes and No Buttons
- Abort, Retry and Ignore Buttons
- Retry and Cancel Buttons
- Help Button with Other Buttons
- Inserting Different Icons with Codes.
- Critical Icon:
- Question Icon:
- Exclamation Icon:
- Information Icon
- How to Customize Title and Prompt in a VBA Message Box?
- How to Insert Multiple Lines inside the Prompt using Line Break?
- VBA Message Box with IF Statement.
1. What is an Excel VBA message Box?
The Excel VBA message box is a function that displays a pop-up window with a message and allows the user to choose from various buttons to respond to the message. The MsgBox function is a useful tool for communicating with the user and requesting input or confirmation in VBA code.
2. Different Parts of an Excel VBA Message Box.
The Excel VBA Message Box has four basic parts in it,
Title- Usually, this is used to make clear what the message box is for. If you don’t specify anything, it shows the name of the application, in this example Microsoft Excel.
Prompt- The prompt is the part of a message box that displays the actual message. Messages or any sort of data can be typed in this space for display.
Button- The default button is OK, but you can change or customize it to show other buttons such as, Yes/No, Retry/Ignore etc.
Close Icon- The close icon is used to close the message box.
3. Syntax and Purpose of Excel VBA Message Box.
Syntax: MsgBox( prompt [, buttons ] [, title ] [, helpfile, context ] )
Purpose: The purpose of the VBA message box is to display information to the user and allow them to interact with it. It is a pop-up window that can be used to display a message, prompt the user for input, or ask them to confirm an action.
4. How to Insert a VBA Message Box and Create Prompt Text?
Step 1: Go to Developer tab and press Visual Basic option. Or you can also right click on the worksheet name and select View Code option.
OR
Step 2: Click on the Insert button and choose Module.
Step 3: Insert the below given code inside the module box and press RUN. Or press F5 key to perform a run.
Sub Simple_MsgBox()
MsgBox “Welcome to Excel 365”
End Sub
Step 4: When you press RUN or F5 key, this msgbox will appear on your screen.
5. Inserting Different Buttons with Codes.
5.1 Ok and Cancel Buttons: Open the VBA Module and insert the following VBA code inside it. Press RUN button or F5 key to perform a run.
Sub MsgBoxOKCancel()
MsgBox “Do You Want To Continue?”, vbOKCancel
End Sub
5.2 Yes and No Buttons: Use this code inside VBA module to generate these buttons.
Sub MsgBoxYesN0()
MsgBox “Do You Want To Continue?”, vbYesNo
End Sub
5.3 Abort, Retry and Ignore Buttons: Insert this code and press RUN button to generate this three-button prompt.
Sub MsgBoxAbortRetryIgnore()
MsgBox “Do You Still Want To Continue?”, vbAbortRetryIgnore
End Sub
5.4 Retry and Cancel Buttons: This following code generate a prompt with Retry and Cancel buttons.
Sub MsgBoxRetryCancel()
MsgBox “What To Do Next?”, vbRetryCancel
End Sub
5.5 Help Button with Other Buttons: The ‘vbMsgBoxHelpButton’ constant can be used to display the help button along with other button constants.
Sub MsgBoxRetryHelp()
MsgBox “What Should We Do Next?”, vbRetryCancel + vbMsgBoxHelpButton
End Sub
6. Inserting Different Icons with Codes.
6.1 Critical Icon: This is the code for default Critical Icon. Insert this inside the VBA Module and press RUN to execute.
Sub MsgBoxCriticalIcon()
MsgBox “This May Remove All Your Data”, vbCritical
End Sub
You can also use the critical icon with other button options with this code.
Sub MsgBoxCriticalIcon()
MsgBox “This May Remove All Your Data. Still Continue?”, vbYesNo + vbCritical
End Sub
6.2 Question Icon: This vbQuestion code is used to generate Question icon inside the prompt.
Sub MsgBoxQuestionIcon()
MsgBox “Closing This Window Without Saving Will Delete Your Data. Do You Want To Continue?”, vbYesNo + vbQuestion
End Sub
6.3 Exclamation Icon: Exclamation icon can be generated with this code.
Sub MsgBoxExclamationIcon()
MsgBox “There Is An Error With This Function”, vbRetryCancel + vbExclamation
End Sub
6.4 Information Icon: Insert the vbInformation code inside the module and press RUN.
Sub MsgBoxInformationIcon()
MsgBox “Do You Want to Retry?”, vbYesNo + vbInformation
End Sub
7. How to Customize Title and Prompt in a VBA Message Box?
Step 1: To customize the prompt, just change the text inside first (“ “) and to change the tile of the message box, change the text inside second (“ “). Then press RUN button after inserting the code inside VBA Module.
Sub MsgBoxQuestionIcon()
MsgBox “Do you want to continue?”, vbYesNo + vbQuestion, “Confirmation”
End Sub
Step 2: Then press RUN button after inserting the code inside VBA Module.
8. How to Insert Multiple Lines inside the Prompt using Line Break?
Step 1: Open the VBA Module and insert the below given code inside it.
Sub YesorNoMultiline()
MsgBox “If you aggree to the terms and conditions, click ‘Yes'” & vbNewLine & “If you do not aggree to the terms and conditions, click ‘No'”, vbYesNo, “Terms & Conditions Confirmation”
End Sub
Step 2: Press RUN button or F5 key.
9. VBA Message Box with IF Statement.
Now we will create a VBA message box which will have an IF statement in it. So, the outcome will be different for different selection.
Step 1: Insert this code inside VBA Module. The that we used here has an IF statement in it and it has Yes and No button arguments. Based on the selection, there are two different statements inside the IF result and Else. Now lets see how this works.
Sub MsgBoxInformationIcon()
Result = MsgBox(“Do you aggree with these terms and conditions?”, vbYesNo + vbQuestion, “Confirmation Tab”)
If Result = vbYes Then
MsgBox “Thank You For Confirming”, , “Confirmed”
Else: MsgBox “Sorry You Are Not Eligible For Our Survices”, , “Denied”
End If
End Sub
Step 2: When you press the RUN or F5 key the code is active and returns this initial message box which we named as Confirmation Tab inside the code. We also inserted this first Prompt text inside the code in the first section (Result).
Step 3: If we press Yes then this prompt will pop-up because we set this inside the code in IF Result.
Step 4: If we select No then another message box will open. By using the IF statement, we can create a series of message boxes based on choices.
For ready-to-use Dashboard Templates:
