Yes. In Excel for Microsoft 365 on Windows, the usual way to create a VBA data-entry popup is to build a UserForm, add input boxes and buttons, then use a macro to write validated entries to a worksheet. For a quick, simple prompt you can use VBA’s InputBox function, but a UserForm is easier to guide and expand when you need several fields.
What an Excel VBA data-entry popup does
A data-entry popup gives someone a small form for entering information without typing directly into the cells of a worksheet. When the person selects Save, VBA checks the entries and transfers them to the next available row. A typical form might collect a customer name, date, and order amount, while a worksheet named Data stores those values in columns A, B, and C.
Excel does not automatically create this kind of custom popup for every workbook. You design it with the Visual Basic for Applications (VBA) editor, which is included with desktop Excel. A UserForm is a VBA form object: you can place controls such as text boxes, labels, and command buttons on it, then add event code that runs when a button is clicked.
This is different from a worksheet data-validation dropdown. Data validation controls what a person can type into a cell, but it does not create a separate window or move a record to a new row by itself. A UserForm can provide that guided entry experience, though it requires a macro-enabled workbook and desktop Excel.
Choose between a UserForm, InputBox, and Excel’s Data Form
There is more than one way to make data entry feel like a popup. The right choice depends on how many fields you need and how much control matters.
- UserForm: Best when the form has multiple fields, validation, instructions, or buttons. It takes more setup, but its layout can be reused and customized.
- InputBox: A built-in VBA prompt that asks for one value at a time. It is suitable for a short, temporary macro, but several InputBox prompts can feel disconnected and are less convenient to validate as a group.
- Excel Data Form: Excel has a built-in form command for entering and reviewing records in a list. It is less customizable than a VBA UserForm and is not the same as a form you design with VBA. The command may need to be added to the Quick Access Toolbar.
- Worksheet-based entry: A formatted table, dropdowns, and data validation can be a better fit when users should work directly in the sheet or when the workbook must avoid macros.
For a repeatable popup with three or more fields, a UserForm is generally the most flexible option. If you only need to ask for one value once, an InputBox may be simpler.
Prepare the workbook and create the form
Before writing code, decide what each field represents and where the saved values should go. For this example, create a worksheet named Data and put these headings in row 1: Name in A1, Date in B1, and Amount in C1. Keeping headings in row 1 makes the records easier to filter and understand.
To create the popup in Excel for Microsoft 365 on Windows:
- Save a copy of the workbook before adding or running macros. Save the working file as an Excel Macro-Enabled Workbook (*.xlsm) so the VBA project can be retained.
- Open the VBA editor with Alt+F11. If the Developer tab is not visible, use File > Options > Customize Ribbon and select Developer.
- In the VBA editor, choose Insert > UserForm. The form designer and Toolbox should appear. If the Toolbox is not visible, open it from the View menu.
- Add three TextBox controls, two CommandButton controls, and optional Label controls. Set the text boxes’ (Name) properties to
txtName,txtDate, andtxtAmount. - Set the buttons’ (Name) properties to
cmdSaveandcmdCancel. Set their Caption properties to Save and Cancel so those are the labels users see. - In the Properties window, give the UserForm a clear (Name), such as
frmEntry, and a Caption such as Add a record. The form’s internal name and its visible title are separate properties.
Control names matter because VBA refers to them in code. If you keep the default names such as TextBox1, either change the code to match or rename the controls as described. Adding labels next to fields is useful because a blank text box does not tell a user what value belongs there.
Add validation and save behavior
Double-click the UserForm’s Save button in the designer to open its click-event procedure. Add the following code there. It requires a name, checks that the date can be interpreted as a date and the amount as a number, then appends the values to the Data sheet.
Private Sub cmdSave_Click()
Dim ws As Worksheet
Dim nextRow As Long
Dim amountValue As Double
' Require a name before saving.
If Trim$(txtName.Value) = vbNullString Then
MsgBox "Enter a name before saving.", vbExclamation
txtName.SetFocus
Exit Sub
End If
' Check the date and amount before writing to the sheet.
If Not IsDate(txtDate.Value) Then
MsgBox "Enter a valid date.", vbExclamation
txtDate.SetFocus
Exit Sub
End If
If Not IsNumeric(txtAmount.Value) Then
MsgBox "Enter a numeric amount.", vbExclamation
txtAmount.SetFocus
Exit Sub
End If
amountValue = CDbl(txtAmount.Value)
Set ws = ThisWorkbook.Worksheets("Data")
' Find the next available row by checking column A.
nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
If nextRow < 2 Then nextRow = 2
ws.Cells(nextRow, "A").Value = Trim$(txtName.Value)
ws.Cells(nextRow, "B").Value = CDate(txtDate.Value)
ws.Cells(nextRow, "B").NumberFormat = "yyyy-mm-dd"
ws.Cells(nextRow, "C").Value = amountValue
MsgBox "Record saved.", vbInformation
Unload Me
End Sub
Private Sub cmdCancel_Click()
' Close the form without saving a record.
Unload Me
End Sub
The example assumes the headings are in row 1 and that every saved record has a value in column A. The next-row calculation uses column A to find the last occupied cell, so do not leave a blank name in an otherwise populated record if you expect later entries to append below it. The date is stored as an Excel date value and displayed in an ISO-like year-month-day format; the amount is stored as a number, not as text with a currency symbol.
IsDate and CDate interpret text according to the computer’s regional settings. For example, a date such as 04/05/2026 can mean April 5 or May 4 depending on the locale. If people in different regions will use the workbook, specify an unambiguous date-entry convention and consider building stricter parsing rather than relying on a slash-separated date.
Open the popup from a worksheet button
A UserForm will not appear simply because it exists in the VBA project; something must show it. Insert a standard module in the VBA editor with Insert > Module, then add a public macro such as this. Replace the form name if yours is different.
Public Sub ShowEntryForm()
' Display the data-entry window.
frmEntry.Show
End Sub
To run it, return to Excel, choose Developer > Macros, select ShowEntryForm, and choose Run. To make it convenient for users, add a shape to a worksheet, right-click the shape, select Assign Macro, and choose ShowEntryForm. A clearly labeled button such as Add record helps users understand how to open the form.
On a Mac, VBA is available in desktop Excel, but keyboard shortcuts and some interface details can differ. The Developer tab may need to be enabled in Excel’s ribbon settings; the Visual Basic command is available from the Developer tab. The Windows Alt+F11 shortcut may require the Fn key or a different keyboard mapping on a Mac, so use the ribbon command if the shortcut does not open the editor. Excel for the web does not run VBA macros, so this popup must be used in desktop Excel.
Improve the form for real data entry
The sample is deliberately small. A production form should reflect the data rules of the workbook, rather than merely accepting whatever looks plausible. For example, if an amount cannot be negative, add a check for that condition. If names must be unique, search existing records before saving. If a field is optional, make that clear and do not apply a required-field check to it.
- Use clear labels and instructions. If Amount means a price in dollars, say so in the label or nearby help text. Avoid relying on column positions to explain what users should enter.
- Set expected formats. For a date field, tell users which date format to enter. For a category, use a ComboBox with a controlled list rather than accepting inconsistent spellings in a text box.
- Consider what happens after saving. This example closes the form. If users enter many records, you might clear the fields and leave the form open instead, or offer a separate Save and New button.
- Keep data in a structured table. Converting the destination range to an Excel Table makes filtering and formatting easier as records grow. Adjust the code if the table’s structure or destination columns differ from this example.
- Handle interruptions thoughtfully. Display a useful message if a required worksheet is missing or protected, rather than leaving a user unsure whether the record was saved.
Validation should happen before the code writes any field to the sheet. That way, a failed check does not leave a partly written record. Also consider whether duplicate submissions are possible: a user may click Save twice if the workbook appears to pause. For important workflows, disable the Save button while processing or check for a matching record before appending.
Common problems and how to fix them
The macro does not appear in the Macros list: Confirm the launcher is in a standard module, is declared Public, and has no arguments. A button’s event procedure belongs to the UserForm, but the macro assigned to a worksheet shape should be in a standard module.
Excel reports that it cannot find the worksheet: The code expects a sheet named exactly Data. Rename the sheet or change the quoted name in the code. Sheet tab names are not the same as a worksheet’s VBA code name.
The form opens but clicking Save gives an error: Check the names of the controls and the form against the code, including spelling. Also check that the Data sheet is not protected in a way that prevents writing to the target cells.
Records overwrite or land in an unexpected row: The example finds the next row using column A. If that column contains gaps or is not populated for every record, choose a column that is consistently filled or use a more suitable table-based approach.
Dates or numbers are rejected unexpectedly: Date and decimal separators can vary with regional settings. For instance, the expected decimal separator may be a period in one locale and a comma in another. Test the form using the same regional conventions as its intended users, and give clear input examples.
The macro is blocked: Excel’s macro security is designed to reduce risk. Do not enable macros in an unfamiliar workbook just to make a popup work. Use a trusted copy from a known source and follow your organization’s security policy.
Safety, saving, and limitations
VBA macros can change workbook data, and running a macro cannot be undone with Ctrl+Z. Save a separate copy before trying code, especially if the workbook contains important records. The sample writes values to cells and does not delete existing data, but a modified macro could do much more, so review code before running it.
Save the workbook as .xlsm to preserve its VBA project. If you save it as a macro-free .xlsx file, Excel does not retain the VBA code. Users also need desktop Excel with macros permitted; opening the file in Excel for the web will not run the VBA form or its buttons. A UserForm is not a security boundary: it guides ordinary entry but does not prevent a user from editing the worksheet or changing the VBA project.
Frequently Asked Questions
Can I make an Excel popup without VBA?
Yes, but it will not be a custom VBA UserForm. Use an Excel Table with data validation and clear instructions, or use Excel’s built-in Data Form command where available. These options avoid VBA, though they offer different layout and automation capabilities.
How do I open a VBA UserForm automatically?
You can call the form’s Show method from a workbook event, such as an event that runs when the workbook opens. Automatic popups can surprise users, however, so a clearly labeled worksheet button is often easier to understand and dismiss.
Can I add dropdowns to the popup?
Yes. Add a ComboBox from the VBA Toolbox, set its name in the Properties window, and populate its choices in the form’s initialization event or from a worksheet range. Validate the selected value if it must match an approved list.
Will the popup work in Excel for the web?
No. Excel for the web does not execute VBA macros or display VBA UserForms. Users need to open the macro-enabled workbook in desktop Excel, with macros allowed by their settings.
Can the form save data to another workbook?
Yes, but the code must identify and open or reference that workbook and handle cases where it is unavailable or read-only. This example uses ThisWorkbook, meaning the workbook that contains the VBA code, and writes to its Data sheet.
Related
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Text to Columns: VBA Setup and ExamplesTo run Excel’s Text to Columns tool with a macro, select the text column, then use VBA’s Range.TextToColumns…
- Excel VBA Progress Bar Without a UserForm: 3 MethodsYes. Excel VBA can show progress without a UserForm by updating Excel’s status bar, writing progress to a…
- Excel Macro Editor: How to Open and Use the VBA EditorThe Excel macro editor is the Visual Basic Editor (VBE), the built-in environment for viewing, writing, and debugging…
- VBA Loop Through Dictionary Keys: Examples and TipsTo loop through the keys in a VBA Scripting.Dictionary, use For Each with a Variant control variable: For…
- Excel VBA JSON: Parse, Read, and Write DataExcel VBA can work with JSON, but VBA has no built-in JSON parser. In desktop Excel, the usual…
- Find Last Empty Row in Excel VBA: Reliable MethodsTo find the next empty row after the data in an Excel column, use End(xlUp) from the bottom…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.