In Excel VBA, InputBox asks a user for a value, while MsgBox displays information or asks the user to choose an action. Use them together when a macro needs a simple response and then must confirm, explain, or act on that response.
What InputBox and MsgBox Do
These are built-in VBA functions for basic interaction between a macro and the person running it. They create small dialog boxes rather than putting questions or messages directly in worksheet cells. A macro might ask for a department name, show a warning before clearing data, or report how many rows it processed.
An InputBox is primarily for collecting a single value. Its prompt is text, and the standard VBA InputBox returns the entered value as a string. A MsgBox is for showing a message, but it can also offer buttons such as Yes and No. Its return value tells the macro which button the user clicked.
Neither function is a worksheet formula. You call them from VBA code, usually in a procedure stored in a workbook. They are useful for small, immediate interactions, but they are not substitutes for a designed data-entry form when a task needs multiple fields, validation rules, or a polished interface.
How VBA InputBox Works
The basic form is InputBox(Prompt, Title, Default). The prompt tells the user what to enter, the title labels the dialog, and the default value is optional. For example, a prompt can request a project code and show a sample code in the entry field. The standard function returns text even if the user types digits; convert that text explicitly if later code needs a number.
A key limitation is that the standard VBA InputBox returns an empty string both when the user presses Cancel and when the user submits a blank entry. The macro cannot distinguish those cases from the return value alone. If that distinction matters, use Application.InputBox, which has different behavior and can constrain the type of entry.
Application.InputBox accepts a Type argument. For example, Type:=1 requests a number, while Type:=2 requests text. Its result should generally be stored in a Variant: pressing Cancel returns the Boolean value False, while a text response can be checked as text. This makes it possible to detect cancellation separately from an empty response.
Input boxes are modal: the user must respond to or dismiss the dialog before continuing to interact with the workbook. Keep the prompt specific, explain the expected format, and avoid asking for information the macro can reliably read from the worksheet already.
How VBA MsgBox Works
A basic MsgBox displays a prompt and an OK button. Its optional arguments let you choose a title and button or icon combination. For instance, vbInformation adds an information icon, while vbExclamation signals a warning. The icon should match the meaning of the message; it does not change the macro’s logic by itself.
When the macro needs a decision, assign the result to a variable declared as VbMsgBoxResult. The result can be compared with constants such as vbYes, vbNo, or vbCancel. Using these named constants is clearer and less error-prone than comparing the result with numeric values.
For example, a Yes/No question can use vbYesNo + vbQuestion for its buttons and icon. Adding vbDefaultButton2 makes the second button the default, which can be a sensible choice for a question where proceeding has consequences. Do not use a confirmation box as the only protection against destructive changes: the macro should still validate what it is about to change.
Use InputBox and MsgBox Together
This example collects a name, distinguishes Cancel from a submitted blank value, and then asks whether to display a greeting. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. The example only displays messages, but that precaution matters especially when adapting it to edit worksheet data.
Sub AskForName()
' Ask for text and distinguish Cancel from a response.
Dim answer As Variant
Dim userName As String
Dim choice As VbMsgBoxResult
answer = Application.InputBox( _
Prompt:="Enter your name:", _
Title:="Name", _
Default:="Your name", _
Type:=2)
If VarType(answer) = vbBoolean Then Exit Sub
userName = Trim$(CStr(answer))
If Len(userName) = 0 Then
MsgBox Prompt:="Enter a name next time.", _
Buttons:=vbOKOnly + vbExclamation, _
Title:="No name entered"
Exit Sub
End If
choice = MsgBox( _
Prompt:="Show a greeting for " & userName & "?", _
Buttons:=vbYesNoCancel + vbQuestion + vbDefaultButton2, _
Title:="Confirm")
If choice = vbYes Then
MsgBox Prompt:="Hello, " & userName & "!", _
Buttons:=vbOKOnly + vbInformation, _
Title:="Greeting"
ElseIf choice = vbNo Then
MsgBox Prompt:="No greeting displayed.", _
Buttons:=vbOKOnly, Title:="Finished"
End If
End Sub
In the code, Application.InputBox is stored in a Variant because its Cancel result is Boolean False. The type check catches that result before the code converts the answer to text. Trim$ removes leading and trailing spaces, and the length check catches a blank or spaces-only response. If the user selects Cancel in the later message box, neither the Yes nor No branch runs, so the procedure simply ends.
Choosing the Right Dialog
Choose the dialog according to the response the macro needs, not just its appearance. A message that only reports success needs no input box. A Yes/No/Cancel message is appropriate when the user must choose among a few clear actions. An input box is reasonable for one short value that can be validated immediately.
- Use standard
InputBoxfor a quick text prompt when treating Cancel and blank input the same way is acceptable. - Use
Application.InputBoxwhen you need to request a particular value type or tell Cancel apart from a response. - Use
MsgBoxfor status, warnings, and simple choices. Check its return value whenever the buttons represent a decision. - Use a UserForm or worksheet-based entry area when the user needs several fields, repeated data entry, or detailed validation and guidance.
Keep choices short and mutually clear. A Yes/No prompt should state exactly what Yes means; if the action is not obvious, label the buttons through a form or provide a more informative workflow rather than relying on a vague question.
Common Problems and Safer Patterns
A common bug is assuming every response is a number because the prompt asked for one. The standard InputBox returns a string, so numeric conversion can fail for letters, blank text, or values outside the expected range. Application.InputBox with Type:=1 can restrict input to a number, but the macro should still check that the value is sensible for the task, such as being positive or within a permitted range.
Another frequent mistake is overlooking cancellation. With the standard function, Cancel and blank input both produce an empty string; with Application.InputBox, Cancel returns Boolean False. Test for the relevant case before using the response. A Yes/No/Cancel message likewise has three possible outcomes, so code should not assume that the user clicked Yes or No.
Prompts can also become confusing when they omit units or formats. Instead of asking for “a date,” say which date is needed and whether the entry should use a recognizable date format. For worksheet edits, validate the response before changing cells, and tell the user what will happen. For large or important changes, consider working on a copy or offering a separate review step.
Finally, dialogs can interrupt automated or unattended workflows. A macro that runs from a scheduled process or processes many records should not unexpectedly pause for a response. In those cases, take input from a known cell, configuration sheet, or form, and report results in a predictable place such as a worksheet or log.
Run VBA in Excel for Windows and Mac
In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, use Insert > Module, paste the procedure into the module, and run it from the editor or from Excel’s macro controls. If the Developer tab is available, Developer > Visual Basic opens the editor. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) so the code is retained.
Excel for Mac also supports VBA in the desktop application, although keyboard behavior depends on the keyboard and function-key settings. Option+F11 may open the editor; on some keyboards, Fn+Option+F11 is needed. The Developer tab can also provide access to Visual Basic. Excel for the web does not run VBA macros, so use desktop Excel to run this code. Some VBA features that depend on Windows-specific components may also need changes for Mac.
Only enable macros in workbooks you trust. A macro can change or delete workbook content, and a message box does not make code safe. Keep a backup before trying unfamiliar code, and do not lower Excel’s macro security settings globally just to run a single workbook.
Alternatives to InputBox and MsgBox
For a reusable, multi-field interface, a VBA UserForm can provide labeled controls, dropdown lists, and validation. It takes more setup than a built-in input box, but it gives users clearer instructions and can prevent invalid choices before a macro proceeds.
For simple workbook workflows, worksheet cells can be easier to review and reuse than a popup. Data Validation can provide a dropdown or restrict entries, while a clearly labeled input area can show instructions alongside the data. If the task is to automate actions across Microsoft 365 without VBA, Office Scripts may be an option in supported environments, but it is a separate scripting approach and does not use VBA’s InputBox or MsgBox.
Frequently Asked Questions
What is the difference between InputBox and MsgBox in VBA?InputBox asks for a value and returns it to the code. MsgBox displays a message and can return a button choice. A macro often uses the first to collect information and the second to explain or confirm what happens next.
How do I detect Cancel in a VBA InputBox?
The standard VBA InputBox returns an empty string on Cancel, so it cannot distinguish Cancel from a blank response. Application.InputBox returns Boolean False on Cancel; store its result in a Variant and check its type before treating it as text or a number.
How can I make a MsgBox show Yes and No buttons?
Set the Buttons argument to vbYesNo, optionally adding an icon constant such as vbQuestion. Store the result and compare it with vbYes or vbNo so the macro responds to the selection.
Can I use VBA InputBox and 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 VBA.
Can an InputBox require a number?
Use Application.InputBox with Type:=1 to request a numeric entry. Still validate the returned value against the needs of the task, and handle Cancel before using it.
Related
- Excel Split Cells Into Multiple Columns
- Excel Split Cell Data Into Multiple Columns
- Excel Split Cell Into First and Last Name: A Clear Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to Code Toggle Buttons in VBATo code a toggle button in Excel VBA, insert an ActiveX Toggle Button, open its worksheet code window,…
- Excel VBA Integer: Range, Limits, and Type ChoiceIn Excel VBA, Integer is a signed whole-number data type that stores values from -32,768 through 32,767. It…
- Excel VBA: Copy and Paste Values Without FormulasTo copy a range’s results without its formulas in Excel VBA, copy the source range and use PasteSpecial…
- VBA For Each Split: Loop Through Text PartsTo loop through text separated by a delimiter in VBA, use For Each with the array returned by…
- VBA Excel Range Variables: How to Declare and Use ThemA VBA Excel Range variable is an object variable that refers to one or more worksheet cells, so…
- Excel VBA Find and Replace: Methods and ExamplesTo find and replace text with Excel VBA, use the Range.Replace method and specify what to search for,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.