To display a message box in VBA, call MsgBox, for example MsgBox "Update complete", vbInformation, "Status". If your macro needs to act on the reader’s choice, assign the result to a variable and test it against values such as vbYes or vbNo.
What a VBA MsgBox Does
MsgBox displays a small dialog containing a message, optional buttons, an optional icon, and an optional title. In Excel, it is commonly used to confirm an action, report that a macro has finished, or warn that an input needs attention. It is a VBA function, not a worksheet formula: you call it from a macro or another VBA procedure, not by typing it into a cell.
The dialog is modal. While it is open, the user must respond to it before the macro continues. That makes a message box useful for a brief decision or notice, but a poor choice for a long explanation or a prompt that should remain open while the user works elsewhere. The function returns a value representing the button selected; if the macro does not need that value, it can simply display the box and continue after the user dismisses it.
Basic Syntax and a First Example
The general syntax is MsgBox(prompt, buttons, title, helpfile, context). The prompt is required. The remaining arguments are optional: buttons specifies which buttons and icon to show, title sets the title-bar text, and helpfile and context connect the dialog to a help file. Most Excel macros need only the first three arguments.
This complete procedure displays an informational message with a custom title:
Sub ShowNotice()
Dim message As String
' Set the text before displaying the dialog.
message = "The report is ready."
MsgBox message, vbInformation, "Report status"
End Sub
In the Visual Basic Editor, place the procedure in a standard module and run it. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the editor; in Excel for Mac, use Option+F11, sometimes with Fn as well depending on the keyboard. In the editor, choose Insert > Module, paste the procedure, and run it from the editor. The exact appearance of the box can vary by Office version and operating system, but its VBA behavior is the same.
The first argument is the message shown inside the dialog. The third argument is the title, not the prompt. If you omit the title, VBA uses a default title that may identify the host application. Supplying a short, specific title helps users understand what the notice relates to.
Choose Buttons, Icons, and Defaults
The buttons argument combines a button arrangement with optional display choices. VBA provides named constants, so use those rather than unexplained numeric values. Common button constants include:
vbOKOnlyandvbOKCancelfor a notice or a simple choice.vbYesNoandvbYesNoCancelfor a decision that needs an explicit response.vbRetryCancelandvbAbortRetryIgnorefor situations where those actions genuinely make sense.vbInformation,vbExclamation,vbCritical, andvbQuestionto show an information, warning, critical, or question icon.vbDefaultButton1,vbDefaultButton2, and the other default-button constants to choose which button is initially selected.
Combine options with + or the VBA Or operator. For example, a confirmation can show Yes and No, use a question icon, and make No the default:
Sub ShowConfirmation()
Dim answer As VbMsgBoxResult
' Combine the button, icon, and default-button options.
answer = MsgBox("Continue with the next step?", _
vbYesNo + vbQuestion + vbDefaultButton2, _
"Confirm action")
End Sub
Choose buttons that match the actions the macro can actually take. A Yes/No box should not imply that choosing No cancels a change if the code proceeds regardless. Similarly, an icon is a visual cue, not a substitute for clear wording. Keep the prompt specific enough that the reader understands the consequence of each option.
Capture and Use the User’s Response
When you assign the result of MsgBox to a variable, declare it as VbMsgBoxResult and compare it with a named return constant. For a Yes/No dialog, the results are vbYes and vbNo. Other button sets return corresponding values such as vbOK, vbCancel, vbRetry, or vbIgnore.
This example runs a separate procedure only when the user chooses Yes:
Sub ConfirmAndContinue()
Dim answer As VbMsgBoxResult
' Ask before starting the operation.
answer = MsgBox("Start the update?", vbYesNo + vbQuestion, "Confirm update")
If answer = vbYes Then
RunUpdate
Else
MsgBox "The update was not started.", vbInformation, "No changes made"
End If
End Sub
Sub RunUpdate()
Dim status As String
' Replace this example action with the required update logic.
status = "Update started."
MsgBox status, vbInformation, "Update status"
End Sub
Testing the returned value is clearer and more robust than checking which button appears first or relying on its position. A dialog with only one possible response usually does not need its result captured. If you use vbYesNoCancel, handle all three possible outcomes so that Cancel is not accidentally treated as permission to continue.
Write Clear, Readable Prompts
A useful message box tells the reader what happened or what decision is needed, then describes the consequence where that is not obvious. For a confirmation, name the action rather than asking a vague “Are you sure?” For a completion notice, state what completed. Keep the title brief and use the prompt for the details.
You can put a line break in the prompt with the VBA constant vbCrLf. Build the text from strings with the ampersand operator. For example, a macro can include a worksheet value in the message:
Sub ShowSelectedSheet()
Dim sheetName As String
Dim message As String
' Read the active worksheet name and add a line break.
sheetName = ActiveSheet.Name
message = "The active worksheet is:" & vbCrLf & sheetName
MsgBox message, vbInformation, "Worksheet"
End Sub
When a value comes from a cell or another source, consider what the user will see if it is blank or unexpected. Convert non-text values deliberately when assembling longer messages, and avoid putting sensitive data into a dialog that may be visible to someone looking at the screen. A message box is meant for concise communication, not for displaying a large table of results.
Run and Test the Macro Safely
For a quick test in Excel on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and run the procedure from the editor with F5. On a Mac, the editor shortcut is commonly Option+F11; keyboard settings can require Fn as well, and available editor shortcuts can differ. You can also run a macro from Excel’s Macro dialog or assign it to a button, provided macros are permitted by the workbook’s security settings.
Save the workbook in a macro-enabled format such as .xlsm if it needs to retain VBA code. Before running any macro that edits data, save a separate copy of the workbook. VBA changes cannot be undone with Ctrl+Z, and the same limitation applies to the equivalent Undo command on Mac. A message box does not make a macro safe by itself: it only pauses for a response, and the code after the response still determines what happens.
Test each available button path. For example, verify that choosing No really leaves the workbook unchanged and that a Cancel choice exits or returns safely. Try the macro on sample data before using it on important workbooks. If a macro is blocked, follow your organization’s security policy rather than enabling code from an unknown or untrusted file.
Common MsgBox Problems and Fixes
- The message does not appear: Confirm that the procedure containing
MsgBoxis actually being run, and check for an earlier error or an exit statement that prevents execution from reaching it. - The code pauses unexpectedly: This is normal while a modal message box is open. The procedure resumes when the user selects a button or closes the dialog.
- The wrong branch runs: Store the result and compare it with the matching named constant, such as
vbYes. Do not compare it with a text label like"Yes"; the function returns a numeric result constant. - The prompt is hard to read: Add deliberate line breaks with
vbCrLf, shorten the wording, and provide a useful title. Avoid building a long sentence from many unclear fragments. - A button or icon option is invalid: Use documented VBA constants and combine compatible options. A button arrangement must make sense for the choices the macro handles.
- The macro will not run: Check that the workbook contains VBA code, is saved in a macro-enabled format when required, and is allowed to run under the applicable Office security settings.
Remember that an ordinary MsgBox cannot provide custom button captions or remain open modelessly while the user interacts with the workbook. If you need those behaviors, use a different interface rather than trying to force them through the built-in dialog.
When to Use an Alternative
Use MsgBox for short, synchronous notices and simple decisions. For a value that the user must type, consider VBA’s InputBox function or Excel’s Application.InputBox, which can be configured for specific types of input. Validate the returned value before using it; an input prompt does not guarantee that the entry is suitable for the macro.
For a form with several fields, custom controls, or a workflow that needs to stay open while the user works, a VBA UserForm is more suitable. A UserForm can be designed with labeled text boxes, buttons, and other controls, and it can be shown modally or modelessly as appropriate. For a simple completion notification, a status-bar message may be less disruptive, while a worksheet cell can be better when the result should remain visible and be saved with the workbook. Choose the lightest interface that communicates the task accurately.
Frequently Asked Questions
How do I display a simple message in VBA?
Call MsgBox with a prompt, such as MsgBox "Done". You can add an icon and title with optional arguments, for example MsgBox "Done", vbInformation, "Status".
How do I make a MsgBox show Yes and No?
Use vbYesNo in the buttons argument, such as MsgBox("Continue?", vbYesNo, "Confirm"). Assign the result to a variable if the macro must respond differently to each choice.
How do I check which MsgBox button was clicked?
Assign the result to a variable declared as VbMsgBoxResult, then compare it with a constant such as vbYes, vbNo, or vbCancel. These are return values, not the button captions as text.
Can I change the text on MsgBox buttons?
No. The built-in VBA message box uses its standard button captions. Use a UserForm if you need custom button labels or a more tailored interaction.
Can I use MsgBox in an Excel cell formula?
No. MsgBox is a VBA function for code, not a worksheet function. Put the call inside a VBA procedure and run that procedure as a macro.
Related
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range Offset: How to Select Cells Relative to a RangeIn VBA, Range.Offset(rowOffset, columnOffset) returns a range shifted from the original by the specified number of rows and…
- Excel VBA Offset: How to Reference Nearby CellsIn Excel VBA, Offset refers to a cell or range a chosen number of rows and columns away…
- Excel Macro Type Mismatch: Causes and FixesAn Excel macro “Type mismatch” is VBA run-time error 13: code tried to use a value as a…
- Excel VBA Format Cell as Percentage: Full GuideTo format a cell as a percentage in Excel VBA, set the cell’s NumberFormat property to "0.00%" (or…
- Microsoft Excel VBA For Loops: Syntax and ExamplesIn Microsoft Excel VBA, a For...Next loop repeats instructions a set number of times, while For Each...Next repeats…
- Replace Excel Macros? Alternatives and When to SwitchYes—many Excel macros can be replaced, but the right substitute depends on the task: use formulas for calculations,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.