In Excel VBA, a UserForm does not have a special “InputBox” control: add a TextBox for entry and buttons such as OK and Cancel, then read the TextBox value in your code. For a quick one-off prompt, use VBA’s built-in InputBox function; choose a UserForm when you need a tailored layout, validation, or several fields.
What “UserForm InputBox” Means
The phrase usually refers to one of two different ways of collecting information in an Excel macro. The built-in InputBox displays a standard prompt with a text field and buttons. A UserForm is a custom dialog that you design in the Visual Basic Editor (VBE), placing controls such as TextBoxes, labels, and command buttons on it.
There is no separate UserForm control named InputBox. To collect text on a form, add a TextBox and use its Value property. For example, a form might show a “Customer name” label beside txtName, with cmdOK and cmdCancel buttons. The form can also collect several values, explain what format is expected, and check entries before accepting them.
When to Use the Built-In InputBox
Use the standard InputBox function when the user needs to answer one simple question and the macro does not need a carefully designed interface. It requires little setup and is convenient for internal utilities or short macros. The function returns the entered value as a string, so the code should convert and validate it if a number, date, or other specific value is required.
A key limitation is that the returned empty string does not tell you whether the user clicked Cancel or submitted a blank response. Those outcomes are indistinguishable through the ordinary VBA InputBox return value. If that distinction matters, or if blank entries need a clear explanation, use a different approach such as a UserForm or Excel’s Application.InputBox.
Application.InputBox is an Excel-specific method with a Type argument that can restrict the kind of input. For instance, it can request a number or let a user select a worksheet range. With Type:=8, the result is a Range object, so assign it with Set. Its Cancel result is Boolean False; because the method can return different types, handle that result deliberately rather than treating every result as text.
When a UserForm Is the Better Choice
A UserForm is useful when a prompt has more than one field, when the user needs instructions or choices, or when entries must be checked before the macro continues. You can label the fields, make a control required, offer a dropdown instead of asking users to type a category, and keep the dialog open after invalid input so the person can correct it.
- Choose a built-in InputBox for a single, uncomplicated response.
- Choose
Application.InputBoxwhen Excel-specific input types, especially selecting a range, are useful. - Choose a UserForm for multiple fields, clearer guidance, custom buttons, or validation.
A form is not automatically better just because it looks more polished. It adds design and code to maintain. If the prompt is a one-time question and a blank-versus-cancel distinction is unimportant, a simple InputBox may be easier for both the author and the user.
Create a Basic Input UserForm
In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic. In the editor, choose Insert > UserForm. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Add controls from the Toolbox and set their names and captions in the Properties window. For a simple name-entry form, use a TextBox named txtName, an OK button named cmdOK, and a Cancel button named cmdCancel.
Put the following event procedures in the form’s code module. They keep track of cancellation, reject a blank name, and hide the form after a valid response. Hiding rather than immediately unloading it lets the calling macro read the entered value.
Option Explicit
Private mWasCancelled As Boolean
Public Property Get WasCancelled() As Boolean
WasCancelled = mWasCancelled
End Property
Public Property Get EnteredName() As String
EnteredName = CStr(txtName.Value)
End Property
Private Sub UserForm_Initialize()
' Treat closing the form without OK as cancellation.
mWasCancelled = True
txtName.Value = vbNullString
End Sub
Private Sub cmdOK_Click()
' Do not accept a name containing only spaces.
If Len(Trim$(CStr(txtName.Value))) = 0 Then
MsgBox "Enter a name before continuing.", vbExclamation
txtName.SetFocus
Exit Sub
End If
mWasCancelled = False
Me.Hide
End Sub
Private Sub cmdCancel_Click()
mWasCancelled = True
Me.Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' Treat the window close button as Cancel.
If CloseMode = vbFormControlMenu Then
Cancel = True
mWasCancelled = True
Me.Hide
End If
End Sub
The QueryClose procedure handles the form’s window close button. Without this handling, users could close the dialog in a way that does not follow the same cancellation path as the Cancel button. You can adapt that behavior if your form needs a different close policy.
Show the Form and Read the Response
Add a separate procedure in a standard module to display the form and use its result. This example assumes the UserForm is named frmEntry. The default vbModal display prevents the macro from moving on until the form is hidden or closed.
Option Explicit
Public Sub AskForName()
Dim dlg As frmEntry
' Create a fresh instance for this prompt.
Set dlg = New frmEntry
dlg.Show vbModal
If Not dlg.WasCancelled Then
MsgBox "Entered: " & dlg.EnteredName, vbInformation
End If
' Read the result before unloading the form.
Unload dlg
Set dlg = Nothing
End Sub
Replace the message box with the action your workbook needs, such as writing the value to a designated cell. Keep the cancellation check: otherwise a macro might treat a dismissed form as a valid submission. If you add more fields, expose their values through additional properties or read them in a deliberate, controlled way before unloading the form.
Validation and Common Design Decisions
A TextBox accepts text; it does not automatically ensure that the entry is a valid date, number, email address, or worksheet value. Decide what counts as valid, explain the requirement near the field, and validate before hiding the form. For example, if the user must enter a whole number, check that the text can be converted and falls within the allowed range. For dates, account for regional date formats instead of assuming every user enters month and day in the same order.
- Trim leading and trailing spaces when they should not be part of the value.
- Use a ComboBox or ListBox for a fixed set of choices rather than relying on users to spell categories consistently.
- Keep the form open after invalid input, show a helpful message, and return focus to the relevant control.
- Decide explicitly whether the X button means Cancel, and make it behave consistently with the Cancel button.
Do not convert a value too early. For example, preserve the original TextBox string until you have checked that it can be interpreted as the required type. Conversion functions and accepted date formats can behave differently depending on the value and the user’s regional settings.
Modal and Modeless Forms
A modal form, shown with vbModal, pauses the calling procedure until the user closes or hides it. That is generally the simplest choice for a question-and-answer dialog because the macro can inspect the result immediately afterward. A modeless form, shown with vbModeless, allows the user to interact with Excel while the form remains open. It is useful for some tools, but the macro must account for the possibility that worksheet data changes while the form is visible.
For a basic data-entry prompt, modal behavior usually avoids unnecessary complexity. A modeless form requires more thought about when values are committed, whether the workbook remains in a valid state, and how the form is closed. Choose it because the workflow requires simultaneous access to the sheet, not simply because it seems more flexible.
Excel Compatibility and Saving the Workbook
VBA macros and UserForms run in desktop Excel, not in Excel for the web. A workbook opened in a browser cannot run its VBA code; open it in the desktop app to use the form. On Windows, the VBE provides the standard UserForm designer and controls. Excel for Mac also supports VBA, but interface details and some controls or automation capabilities can differ, so verify a form on the Mac versions your audience actually uses. ActiveX controls are not a cross-platform substitute for ordinary UserForm controls.
Save a macro-enabled workbook as .xlsm if it needs to retain VBA. Macro security settings may require the user to allow macros or use a trusted location, according to their organization’s policy. Before running a macro that writes to or changes workbook data, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, and the same undo limitation applies to Mac users even though the keyboard shortcut differs.
Frequently Asked Questions
Can I put an InputBox inside a VBA UserForm?
No special InputBox control is built into a UserForm. Add a TextBox to the form to collect text, then use its value in your code. You can call the standard InputBox from VBA code, but that displays a separate dialog rather than embedding an input field in the form.
How do I tell whether a user clicked Cancel in InputBox?
With the VBA InputBox function, Cancel and an accepted blank response both return an empty string, so the return value alone cannot distinguish them. A UserForm can track whether OK or Cancel was used. Excel’s Application.InputBox returns Boolean False when canceled, so handle its potentially different return types carefully.
How do I get a value from a TextBox in a UserForm?
Refer to the control by its name and read its Value property, such as txtName.Value. A common pattern is to hide the form after OK, read the value in the calling procedure, and then unload the form.
Can a UserForm validate an entry before closing?
Yes. Put the checks in the OK button’s click procedure. If an entry fails, show an explanation, keep the form visible, and set focus to the control that needs attention. Hide or unload the form only after the response passes validation.
Will a VBA UserForm work in Excel for the web?
No. Excel for the web does not run VBA macros or display their UserForms. Use desktop Excel for a VBA-based workflow, or consider a web-compatible alternative if users must work entirely in a browser.
Choosing the Simplest Reliable Option
Use the built-in InputBox for a quick, single text prompt when its limits are acceptable. Use Application.InputBox when Excel’s typed input options or range selection solve the problem. Build a UserForm when the interaction needs multiple fields, clear validation, or a consistent Cancel path. Whichever method you choose, make cancellation explicit, check values before using them, and test the workbook in the desktop Excel environments where it will be used.
Related
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel SEQUENCE Macro: Generate Number Series in VBAIf you want a sequence of numbers in Excel, the simplest option is usually the SEQUENCE worksheet function,…
- Excel Macro to Hide Columns: VBA Examples and TipsTo hide columns with a VBA macro in Excel for Microsoft 365 on Windows, put Columns("B:D").Hidden = True…
- VBA Dictionary Exists and Add: How They WorkYes. In a VBA Dictionary, use Exists to check whether a key is already present and Add to…
- Excel VBA: Save a Workbook as a New File with SaveAsTo save an Excel workbook as a new file with VBA, use the workbook’s SaveAs method and supply…
- Excel VBA Find Text in a Range: Code and Examples ExplainedTo find text in an Excel range with VBA, use the range’s Find method and check whether it…
- VBA Close Workbook Without a Save PromptTo close an Excel workbook without a save prompt, specify what should happen to unsaved changes: use Workbook.Close…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.