VBA Message Box in Excel is a powerful tool for communicating with users. By displaying messages, warnings, or prompts, it enhances user interaction and provides valuable feedback during macro execution. With customizable options such as button styles and icon types, the VBA Message Box offers flexibility in tailoring the user experience. It can be employed for various purposes, including error handling, input validation, and informational alerts. Whether you’re developing complex automation scripts or simple macros, integrating Message Boxes can greatly improve the usability and functionality of your Excel applications. So, leverage the VBA Message Box in Excel to streamline workflows, guide users, and ensure a smooth and intuitive interaction process.
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.
- Error Handling: Use Message Boxes to alert users about errors encountered during VBA macro execution, providing informative messages and suggestions for resolution.
- Input Validation: Employ Message Boxes to validate user input, ensuring that only valid data is entered into Excel worksheets or forms.
- Confirmation Dialogs: Utilize Message Boxes to prompt users for confirmation before executing critical actions, such as deleting data or saving changes.
- Informational Alerts: Display Message Boxes to convey important information or updates to users, such as reminders about upcoming deadlines or system maintenance.
- Debugging: Insert Message Boxes into VBA code to output variable values or execution statuses, aiding in debugging and troubleshooting processes.
- Customized User Interaction: Customize Message Boxes with different button styles, icon types, and message formats to create a tailored user experience that matches your application’s requirements and branding.
For ready-to-use Dashboard Templates:
