An Excel VBA error message box is usually created with the MsgBox function; use On Error GoTo to catch a runtime error and display details from the Err object. In Excel for Microsoft 365 on Windows, put the code in a VBA procedure and run the macro; Excel for the web cannot run VBA macros.
What an Excel VBA message box does
MsgBox displays a dialog that pauses the current macro until the user responds. It is useful for explaining that an input is missing, asking a user to confirm an action, or reporting that a procedure could not finish. A message box is not itself an error handler: it only shows information. If an unhandled runtime error occurs, Excel may stop the macro and show its own error dialog instead.
There are therefore two related tasks: show a message at a point your code already knows is a problem, or catch an unexpected VBA error and then show a message. The first is a normal decision in your procedure; the second uses VBA error-handling statements such as On Error GoTo. Choosing the right approach makes the message more useful and keeps the macro’s behavior predictable.
Display a simple error message
Save a copy of the workbook before running any macro; VBA changes cannot be reversed with Ctrl+Z. In Excel for Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and paste in a procedure. The Developer tab’s Visual Basic button also opens the editor. On a Mac, use Tools > Macro > Visual Basic Editor; Option+F11 may work, with Fn as well on some keyboards.
This example displays a prompt when an input is not suitable. It uses a warning icon and lets the user acknowledge the message or cancel the procedure:
Sub ShowInputError()
Dim response As VbMsgBoxResult
' Tell the user what needs attention.
response = MsgBox("Enter a whole number before continuing.", _
vbExclamation Or vbOKCancel, "Input needed")
If response = vbCancel Then
Exit Sub
End If
End Sub
In the example, the macro exits if the user clicks Cancel. The message itself does not validate a worksheet cell; real validation code would first read the relevant value, check it, and show this dialog only when the check fails. For example, a procedure could test whether a required cell is blank before performing a calculation. That separation keeps the message connected to an actual condition rather than showing it every time the macro runs.
Choose buttons, icons, and a title
The basic syntax is MsgBox(prompt, buttons, title). The prompt is the text shown to the user, the buttons argument controls which buttons and icon appear, and the title is the dialog caption. The latter two arguments are optional, but specifying them makes the message clearer. A plain message can use vbOKOnly; a warning can use vbExclamation; and a serious failure can use vbCritical. Use vbInformation for a neutral notice and vbQuestion for a genuine question.
To ask the user to choose, combine button and icon constants with Or, such as vbYesNo Or vbQuestion. The function returns a value of type VbMsgBoxResult. Test that result against named constants like vbYes, vbNo, vbOK, or vbCancel, rather than relying on numeric values. You can also combine a default-button constant, such as vbDefaultButton2, when the second button should be selected by default.
Choose buttons that match the action. Do not present Yes and No if both choices lead to the same outcome, and do not make a destructive choice the default. A message box is modal by default: the user must respond before continuing to work in that Excel window. This is usually appropriate for a brief error notice, but repeated dialogs in a loop can be disruptive.
Catch a runtime error with On Error GoTo
For an error that may occur while a procedure runs, direct VBA to a labeled error handler. The following intentionally divides by zero to demonstrate the handler. In ordinary code, replace the calculation with the operation you need to protect. VBA reports division by zero as runtime error 11.
Sub CalculateRatio()
Dim numerator As Double
Dim denominator As Double
Dim result As Double
Dim errorNumber As Long
Dim errorText As String
On Error GoTo ErrorHandler
numerator = 10
denominator = 0
result = numerator / denominator
MsgBox "Result: " & CStr(result), vbInformation, "Calculation complete"
Exit Sub
ErrorHandler:
' Save the error details before showing the dialog.
errorNumber = Err.Number
errorText = Err.Description
MsgBox "The calculation could not be completed." & vbCrLf & _
"Error " & CStr(errorNumber) & ": " & errorText, _
vbCritical, "Calculation error"
End Sub
On Error GoTo ErrorHandler tells VBA where to continue if a runtime error happens. Err.Number gives the error number and Err.Description gives its description. The Exit Sub before the label is important: without it, a successful calculation would continue into the handler and display an error message even though nothing failed. Storing the error details in variables before calling other code is also a good habit when you need to report them.
A real handler may also need to close an opened file, restore an application setting, or release an object before displaying the message. Keep that cleanup deliberate, and avoid hiding the original error while trying to clean up. For a procedure with several possible failure points, the handler can report the operation that failed as well as the VBA error details, so the user has a practical next step.
Write an error message people can act on
VBA’s description may be useful to someone editing the macro, but it can be confusing to a workbook user. A strong message states what failed, identifies the relevant item when possible, and explains what to do next. For example, “The value in cell B4 must be a date. Enter a date and run the report again” is more helpful than “Type mismatch.” For a technical support workflow, include the error number and description after the plain-language explanation.
- Say what could not be completed, such as saving a report or reading a required value.
- Give a next action, such as checking a named cell, selecting a valid folder, or contacting the workbook owner.
- Include only details the reader needs. Avoid putting passwords, confidential data, or entire records into a dialog.
- Use a concise title that identifies the task, such as “Import problem” or “Report could not run.”
Consider whether a message box is the best place for the information. If a macro processes many rows, showing one dialog per bad row can make the workbook difficult to use. It may be better to collect the row numbers and problems, then show one summary at the end or write the details to a worksheet. A message box is most effective for a single decision or a short, important notice.
Common problems and misconceptions
- The message appears but the macro continues. That is expected unless the code exits, branches, or otherwise handles the result. A message box does not automatically stop the rest of a procedure after the user clicks a button.
- The macro shows a second error dialog. The custom handler may itself contain an error, or execution may have fallen through into the handler after normal work. Check for an
Exit SuborExit Functionbefore the handler label. - On Error Resume Next is used to silence everything. It tells VBA to continue after an error, which can leave later code using incomplete or invalid data. If it is needed for one expected operation, keep its scope narrow, check
Err.Numberimmediately, and restore normal handling withOn Error GoTo 0. - Application.DisplayAlerts is expected to hide MsgBox. That property controls certain Excel application alerts; it does not suppress a VBA
MsgBoxcall. Remove or change the explicit call if the macro should not display that dialog. - The code will not run in a browser. Excel for the web does not execute VBA macros. Open a macro-enabled workbook in the desktop Excel app to run VBA.
When a macro appears to stop at an error, click Debug in the VBA error dialog if that option is available. The editor highlights the line where execution stopped. Inspect the values used on that line and check whether the error is caused by an unexpected blank cell, a wrong data type, a missing worksheet, or a file path that is not available. Do not simply suppress an error before understanding what caused it.
Run and distribute the macro safely
Save a workbook that contains VBA in an Excel Macro-Enabled Workbook (.xlsm) format. If you save it as a standard .xlsx file, VBA code is not retained. On Windows, you can run a selected macro from Developer > Macros or press Alt+F8. On Mac, use Tools > Macro > Macros. The VBA editor and macro availability differ from Excel for the web, which can store a macro-enabled file but cannot run its VBA code.
Only enable macros in files from sources you trust. A message box is just a user interface; it does not make a macro safe or prevent the macro from changing workbook data. Before using a macro on important work, keep a separate copy and review what the code does. For a team workbook, explain which macros users should run and what inputs they require. If a file is blocked by an organization’s security policy, follow the organization’s approved process rather than changing security settings broadly.
Frequently Asked Questions
How do I show an error message in Excel VBA?
Call MsgBox with a prompt, an appropriate icon or button choice, and a title. For example, MsgBox "A required value is missing.", vbExclamation, "Input error" displays a warning message. Put it inside a condition so it appears only when the input actually fails validation.
How do I include the VBA error number and description?
Inside an error handler, read Err.Number and Err.Description, then include them in the prompt passed to MsgBox. Store the values first if the handler will call other procedures, and include a plain-language explanation so the user is not left with a technical description alone.
Does MsgBox stop a VBA macro?
It pauses the procedure while the dialog is open. After the user clicks a button, VBA continues at the next statement unless the code checks the returned choice, exits the procedure, or takes another branch. Use an explicit Exit Sub when the macro should end.
Can a VBA message box have Yes and No buttons?
Yes. Use vbYesNo in the buttons argument, usually combined with a suitable icon such as vbQuestion. Check the returned value against vbYes or vbNo so each response produces the intended action.
Why does my VBA error message not appear in Excel for the web?
Excel for the web does not run VBA macros, so a procedure containing MsgBox cannot execute there. Open the workbook in the desktop version of Excel to run the macro; if the workbook must work in a browser, consider an Office Script or another supported approach instead.
Related
- Excel Task List Template: Set Up and Customize Yours
- Excel Template for a Budget Plan: Build Your Own
- Excel Meal Planning Template: A Practical Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find After: How Excel Chooses the Search Start CellIn Excel VBA, the After argument in Range.Find tells Excel which single cell to search after when beginning…
- Excel VBA Dictionary For Each: Looping Through KeysIn Excel VBA, For Each loops through the keys of a Scripting.Dictionary when you write For Each key…
- Excel UserForm Checkbox: Add, Code, and TroubleshootAn Excel UserForm checkbox is a clickable VBA control that lets someone choose a yes-or-no option in a…
- VBA AutoFilter Delete: Remove Visible Rows Safely in ExcelYes. In Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data rows, then delete those rows; leave the header…
- Can Google Sheets Run Excel Macros? Key DifferencesNo. Google Sheets cannot run Excel VBA macros. It has its own automation tools—Google Apps Script and a…
- VBA Active Workbook: Meaning, Use, and ExamplesIn Excel VBA, ActiveWorkbook refers to the workbook that is currently active in the Excel window. Use it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.