In Excel VBA, the basic MsgBox syntax is MsgBox(prompt, [buttons], [title], [helpfile], [context]). For a simple message, call MsgBox with just the prompt; add button and icon options when needed, and assign its result to a variable if your macro must respond to the user’s choice.
What the MsgBox Syntax Means
MsgBox displays a dialog box while a VBA macro is running. The square brackets in the syntax indicate optional arguments; do not type the brackets themselves. The first argument, prompt, is the message to display. You can provide a string directly or pass a string variable. If you omit the remaining arguments, VBA shows an OK button and uses a default title.
In VBA, MsgBox can be used as a statement when you only want to show information, or as a function when you need to capture which button the user clicked. Its full argument list is:
prompt: Required text for the message. It can contain line breaks.buttons: Optional value that selects the button arrangement, icon, default button, and some dialog behavior.title: Optional text displayed in the dialog’s title bar.helpfileandcontext: Optional help-file information. If you specify a help file, you must also specify a context number.
Most Excel macros need only the first three arguments. Help-file arguments are for applications that provide a compatible help file; they are not necessary for ordinary confirmation or information prompts.
Choosing Buttons, Icons, and a Default
The buttons argument accepts VBA constants. You can combine a button-set constant with an icon constant and a default-button constant. A common combination is vbYesNo + vbQuestion + vbDefaultButton2: it displays Yes and No buttons, a question icon, and makes No the default. The default is the button activated by pressing Enter, so choose it deliberately when the choice has consequences.
- Button sets include
vbOKOnly,vbOKCancel,vbYesNo,vbYesNoCancel,vbRetryCancel, andvbAbortRetryIgnore. - Common icons are
vbInformation,vbQuestion,vbExclamation, andvbCritical. - Default-button constants include
vbDefaultButton1,vbDefaultButton2, and, for a set with three buttons,vbDefaultButton3.
Use one button-set constant and choose an icon that matches the situation. For example, an informational completion notice usually needs vbInformation; a question that asks whether to continue may use vbYesNo + vbQuestion. An icon does not change what the buttons do, and a question icon does not make the dialog return a special answer.
A Basic MsgBox Macro
This example displays a message with an explicit title and information icon. In the Visual Basic Editor, put the procedure in a standard module, such as one created with Insert > Module. Before running any macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.
Sub ShowCompletionMessage()
' Tell the user that the task has finished.
Dim messageText As String
messageText = "The report is ready."
MsgBox messageText, vbOKOnly + vbInformation, "Report status"
End Sub
Here, vbOKOnly makes the button choice explicit, and vbInformation selects the icon. The title argument follows the buttons argument, so if you want to set a title but leave the button options at their default, use a comma placeholder: MsgBox "Saved", , "Workbook status". In a reusable macro, an explicit button constant can make the intended dialog easier to read.
Reading the User’s Response
When the user’s choice should affect what happens next, treat MsgBox as a function and store its return value. The returned value identifies the button clicked; it is not the button’s displayed text. Compare it with named constants such as vbYes or vbCancel, rather than relying on numeric values.
Sub AskBeforeContinuing()
' Continue only when the user selects Yes.
Dim answer As VbMsgBoxResult
answer = MsgBox("Continue with the update?", _
vbYesNo + vbQuestion + vbDefaultButton2, _
"Confirm update")
If answer = vbYes Then
MsgBox "The update can proceed.", vbOKOnly, "Confirmed"
Else
MsgBox "No changes were made.", vbOKOnly, "Cancelled"
End If
End Sub
VbMsgBoxResult is the VBA type used for this result. In this example, the Yes/No set returns a result that the If statement can compare with vbYes or vbNo. For a three-button dialog, check each relevant outcome, including Cancel, rather than assuming every response means approval. A MsgBox is a prompt, not a substitute for validating data or confirming that a later operation succeeded.
Adding Line Breaks and Dynamic Text
For a message with multiple lines, join text with vbCrLf or vbNewLine. These VBA constants make the break clear in code. You can also combine a fixed message with a variable, such as a worksheet name or a count. Use the ampersand operator (&) to join strings.
Sub ShowWorksheetDetails()
' Display the active sheet name on a separate line.
Dim messageText As String
messageText = "The active worksheet is:" & vbCrLf & ActiveSheet.Name
MsgBox messageText, vbOKOnly + vbInformation, "Worksheet details"
End Sub
Keep the prompt concise. MsgBox is a small modal dialog, not a place for a long report or a complex form. If users need to review many rows, edit several values, or choose from a long list, put that interaction in a worksheet or a UserForm instead.
Where to Put and Run the Macro
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. Return to Excel and run a macro from Developer > Macros, select its name, and choose Run; Alt+F8 opens the Macro dialog. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer.
On Excel for Mac, use Developer > Visual Basic to open the editor; keyboard shortcuts can vary with the keyboard and function-key settings. The Mac version supports VBA macros, but its menus and security prompts can differ from Windows. Excel for the web does not create, edit, or run VBA macros. Open the workbook in the desktop Excel application to use its VBA code.
To retain VBA code, save the workbook in a macro-enabled format such as .xlsm. Saving as a standard .xlsx workbook does not preserve VBA macros. Keep a separate backup when changing code or testing a procedure that modifies cells, sheets, or files.
Common MsgBox Problems
A missing or unexpected dialog often comes from a small syntax or logic issue. Check the following before changing the code:
- Arguments are in the wrong order. The title is the third argument, after the buttons value. A title placed second is interpreted as the buttons argument.
- A response is not captured. Calling
MsgBoxalone displays the dialog, but the macro cannot branch on the choice unless the function result is assigned to a variable. - The button set does not match the comparisons. If the dialog offers Yes and No, test for
vbYesandvbNo; do not test for a button the dialog did not show. - Text is not joined correctly. Use
&to concatenate strings and usevbCrLffor a line break. A plus sign is used to combine button-option constants, not as the general string-joining operator. - The macro cannot run. Confirm that the workbook is open in desktop Excel, the file retains its macro-enabled format, and the macro is allowed by your organization’s security settings.
Also avoid using a MsgBox as a recurring status display in a loop. Each dialog pauses the macro until someone responds, which can make bulk processing slow or appear stuck. For routine progress, consider writing a status to the worksheet or using Excel’s status bar instead.
Safety and Alternatives
Macros can change workbook content, so only enable VBA from a source you trust. Do not lower security settings or enable macros simply because a file displays a prompt asking you to do so. Follow your organization’s policy, check the file’s source, and keep an unchanged copy before running code. A MsgBox itself does not make a macro safe; it only displays a dialog and, optionally, returns the selected button.
Use MsgBox for short, blocking messages and simple decisions. For a prompt that needs an input value, VBA’s InputBox function may be more suitable, though its result still needs validation. For a richer interface with multiple fields, options, or explanatory text, a UserForm offers more control. For messages that should not interrupt work, a cell note, worksheet status area, or the Excel status bar may be a better fit.
Frequently Asked Questions
What is the simplest Excel VBA MsgBox syntax?
Use MsgBox "Your message" inside a VBA procedure. It displays the text with a default OK button. Add optional arguments for a title, icon, or different button set.
How do I add a title to a VBA MsgBox?
Put the title in the third argument: MsgBox "Saved", vbOKOnly, "Status". The second argument is for button and display options, so it cannot be skipped positionally if you want to supply the third argument.
How do I check whether the user clicked Yes?
Assign the result to a variable and compare it with vbYes. The dialog must include a Yes button, for example with vbYesNo in its buttons argument.
Can an Excel macro MsgBox have multiple lines?
Yes. Join the lines with vbCrLf or vbNewLine, for example "First line" & vbCrLf & "Second line".
Can I run a VBA MsgBox in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the procedure.
Related
- SharePoint Corrupted Excel File? Recovery Steps
- Ctrl+K in Excel: How to Insert and Edit Hyperlinks
- Correlation Analysis in Excel: Methods and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Loop Without Do: Choosing For, While, or For EachYes. In VBA, you can loop without using Do: use For...Next for a known number of repetitions, For…
- Automatically Add Comments to Excel Cells with VBAYes. In desktop Excel, VBA can automatically add a note to a cell with Range.AddComment; in current Microsoft…
- Excel VBA Error Handler: How to Catch and Fix ErrorsAn Excel VBA error handler is code that tells a macro what to do when a runtime error…
- Excel VBA Array Count: Find the Number of ElementsIn Excel VBA, count the elements in an array with UBound(array) - LBound(array) + 1 for a one-dimensional…
- VBA Sort Collection: Methods and Practical ExamplesVBA’s Collection object has no built-in sort method. To sort a collection, copy its items into an array,…
- Call a Macro from an Excel UserForm ButtonYes. In Excel for Microsoft 365 on Windows, put a call to a Public Sub in the UserForm…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.