VBA InputBox vs MsgBox: OK or Cancel Explained

Yes: VBA’s standard InputBox already displays OK and Cancel buttons, while MsgBox can display them when you specify vbOKCancel. The important difference is what Cancel returns: the standard InputBox returns an empty string, which is indistinguishable from clicking OK after leaving the input blank; MsgBox returns a distinct result such as vbCancel.

InputBox and MsgBox do different jobs

Both dialogs can offer a user a way to continue or back out, but they are designed for different kinds of interaction. Use an InputBox when you need the user to type a value, such as a name, date, or search term. Use a message box when you need to show information or ask the user to choose among button responses.

Their similar-looking buttons can cause confusion. The standard VBA InputBox function always supplies OK and Cancel; it has no buttons argument for replacing those buttons with Yes and No. MsgBox, in contrast, lets you select a button set with its second argument. You can use OK and Cancel, Yes and No, or Yes, No, and Cancel, among other combinations.

This distinction matters in code. A dialog’s appearance does not tell the whole story: a procedure must inspect the value the dialog returns and decide what to do when the user backs out.

Show OK and Cancel with MsgBox

In Excel VBA, the MsgBox function returns a VbMsgBoxResult value. When you supply vbOKCancel, the return value identifies which button was clicked: vbOK or vbCancel. Test that result before continuing.

Sub ConfirmReport()
    Dim response As VbMsgBoxResult

    ' Ask before continuing with the report.
    response = MsgBox("Continue with the report?", vbOKCancel + vbQuestion, "Confirm")

    If response = vbCancel Then
        Exit Sub
    End If

    MsgBox "The procedure can continue.", vbInformation
End Sub

The vbQuestion constant adds an icon; it does not change the buttons or the meaning of the return value. The button choices are controlled by vbOKCancel. You can also use vbYesNo or vbYesNoCancel when those choices better describe the decision. For the latter, test all three possible results explicitly.

A message box with only OK is the default, so specifying vbOKCancel is necessary if the user needs a Cancel button. The constants are more readable than numeric values: comparing the result with vbCancel makes the code’s intention clear.

What the standard VBA InputBox returns

The built-in InputBox function prompts for text and returns a String. Clicking OK returns whatever is in the text field. Clicking Cancel returns a zero-length string, written as "". If the user clicks OK without entering anything, that also returns a zero-length string.

Sub AskForName()
    Dim enteredName As String

    ' Ask for text; OK and Cancel are built into InputBox.
    enteredName = InputBox("Enter a name:", "Name")

    If Len(enteredName) = 0 Then
        MsgBox "No name was provided, or the prompt was cancelled.", vbInformation
        Exit Sub
    End If

    MsgBox "You entered: " & enteredName, vbInformation
End Sub

This code safely avoids treating an empty response as a name, but it cannot tell whether the user clicked Cancel or clicked OK with the field blank. That is a limitation of the function’s return value, not a mistake in the comparison. Checking Len(enteredName) = 0 only tells you that the returned text is empty.

The InputBox function has optional arguments for its prompt, title, default text, screen position, and help information. For example, a nonempty default can make the expected format clearer. However, a default value does not reliably solve the Cancel ambiguity: users may delete the default and click OK, and the result can again be an empty string.

Excel Application.InputBox can distinguish Cancel

Excel provides a second option: Application.InputBox. It is an Excel-specific method, rather than the standard VBA InputBox function. It can restrict the kind of response accepted, and pressing Cancel returns the Boolean value False. With a text prompt, that makes it possible to distinguish Cancel from an accepted blank response by storing the result in a Variant and checking its type.

Sub AskForTextInExcel()
    Dim answer As Variant

    ' Type 2 requests text. Save a copy before running any macro.
    answer = Application.InputBox(Prompt:="Enter a project code:", _
                                  Title:="Project code", Type:=2)

    If VarType(answer) = vbBoolean Then
        If answer = False Then
            MsgBox "You cancelled the prompt.", vbInformation
            Exit Sub
        End If
    End If

    If Len(CStr(answer)) = 0 Then
        MsgBox "You accepted the prompt without entering text.", vbInformation
        Exit Sub
    End If

    MsgBox "Code entered: " & CStr(answer), vbInformation
End Sub

Here, Type:=2 asks for text, and the Variant preserves the distinction between a Boolean result and a text result. If the response is text, including an empty string, the code follows the text path. If the user cancels, the result is Boolean False. Avoid assigning this result immediately to a String: converting it can lose the type information that makes the distinction useful.

Application.InputBox has other useful Type values. Type 1 requests a number, Type 4 a logical value, and Type 8 a range reference. Other values support formulas, error values, or arrays, and certain types can be combined. For a range prompt, store the result in a Variant first so cancellation can be checked before using the selected range; range handling is different from ordinary text input.

How to choose the right dialog

Choose based on what the user needs to provide and whether your code must know that Cancel was clicked:

Use MsgBox for a short decision or acknowledgement, such as confirming whether to continue. Choose the button set that matches the question.
Use the standard InputBox for a simple text response when treating Cancel and blank input the same is acceptable.
Use Excel Application.InputBox when you need to control the accepted input type or distinguish cancellation from an accepted blank text response.
Use a UserForm when you need several fields, validation messages, custom buttons, or more control over how the dialog looks and behaves.

For a confirmation, a Yes/No or Yes/No/Cancel MsgBox may read more naturally than OK/Cancel. For a value that must not be blank, an InputBox can be followed by validation, but consider whether a form would make the requirement clearer. A dialog should make the consequence of each button understandable; Cancel should normally leave the underlying operation uncommitted.

Common mistakes and safer patterns

A frequent error is assuming that Cancel returns the number zero or the Boolean value False from the standard InputBox. It does not: the standard function returns a String, and its Cancel result is an empty string. The Boolean False cancellation result belongs to Excel’s Application.InputBox, not the ordinary VBA function.

Another mistake is comparing a MsgBox result with text such as "Cancel". MsgBox returns a result constant, not the label of the button. Compare against vbCancel, vbOK, vbYes, or the constant matching the buttons you displayed. A Select Case statement is often clearest when there are three choices.

Do not proceed with a destructive action just because a dialog closed. Check the returned value first.
Do not assume blank text means Cancel when using the standard InputBox; it may mean the user accepted an empty field.
Validate the entered value after the dialog. A text prompt does not guarantee that the response is a valid date, number, filename, or worksheet name.
When a Cancel and a blank response need different outcomes, select a dialog whose return value preserves that distinction.

VBA macros cannot be undone with Ctrl+Z. Save a copy of the workbook before running a macro, especially if you add code that edits cells, deletes data, or changes workbook structure. The sample procedures above show dialog handling, but real procedures may perform additional actions after a user confirms.

Platform and scope

The standard VBA InputBox function and MsgBox function are available in VBA across Office desktop applications. Their basic return-value behavior is not specific to Excel. Application.InputBox, however, refers to the Excel application object and its typed-input features; do not assume that the same method is available in Word or other Office hosts.

Excel for Microsoft 365 on Windows supports the examples shown. Excel for Mac also has VBA and the basic dialog functions, though some surrounding features and platform interactions can differ. Excel for the web does not run VBA macros, so these VBA prompts are not a way to collect input from users working in a workbook in the browser.

Frequently Asked Questions

Does VBA InputBox have an OK or Cancel button?
The standard InputBox function displays both OK and Cancel by default. You cannot choose a different button set for that function.

How do I make a VBA MsgBox show OK and Cancel?
Pass vbOKCancel as the buttons argument, for example MsgBox("Continue?", vbOKCancel). Store the result and compare it with vbCancel or vbOK.

What does InputBox return when I click Cancel?
The standard VBA InputBox returns a zero-length String. Clicking OK with the field empty returns the same value, so that function alone cannot distinguish those two actions.

How can I tell Cancel from blank input in Excel VBA?
Use Excel’s Application.InputBox with Type:=2, store its result in a Variant, and check whether VarType is vbBoolean. A Boolean False indicates cancellation; a text result can be checked separately for an empty value.

Can I put Yes and No buttons on an InputBox?
Not on the standard InputBox function. It is an input prompt with OK and Cancel. Use MsgBox with vbYesNo for a two-choice question, or build a UserForm if you need both typed input and custom choices.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.