Excel VBA UserForm coding means creating a custom dialog in Visual Basic for Applications and writing code that responds to its controls. In Microsoft 365 for Windows, you can build a simple data-entry form by adding a UserForm in the VBA editor, placing text boxes and buttons on it, and using a button’s click event to validate and save entries to a worksheet.
What an Excel VBA UserForm Does
A UserForm is a VBA form window that appears when a macro opens it. It can collect information, display choices, and guide a person through a task without requiring them to type directly into worksheet cells. For example, a small team could use a form to enter a person’s name, email address, department, and an optional amount, with each submission appended as a new row in a records sheet.
Forms are useful when a workbook needs a more controlled input process than a blank grid provides. They can make required fields obvious, offer fixed choices in a drop-down list, and show a message when information is missing. They do not, by themselves, make a workbook a database or prevent every kind of bad data. A worksheet, its permissions, and the form’s validation still need to be designed appropriately for the people and information involved.
How UserForms and VBA Events Work
A form contains controls, such as text boxes, labels, combo boxes, and command buttons. Each control has properties, including its name, caption, size, and position. VBA code can read or change those properties. The code also uses event procedures: for instance, btnSave_Click runs when a user clicks a button named btnSave.
The usual sequence is to open the form, initialize its controls, collect values, check them, and then write them to a worksheet. The form is a front end; the worksheet is where this example stores the records. Keeping the worksheet column order and the code’s output order consistent makes later sorting, filtering, and reporting easier.
Create the Form and Add Its Controls
First save a working copy of the workbook as an Excel Macro-Enabled Workbook (.xlsm). On Windows, show the Developer tab from File > Options > Customize Ribbon by selecting Developer. Then press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > UserForm. If the Toolbox is not visible, use View > Toolbox.
Add the controls below by choosing them in the Toolbox and drawing them on the form. Select each control and use the Properties window to set its (Name) property. The name is what VBA uses; the caption is the text a user sees.
- Add four labels and four input controls: text boxes named
txtName,txtEmail, andtxtAmount, plus a combo box namedcboDepartment. - Add command buttons named
btnSave,btnClear, andbtnCancel. Set their Caption properties to Save, Clear, and Cancel. - Set the form’s
(Name)property tofrmEntryand its Caption property to something descriptive, such as New record. - Create a worksheet named
Records. Put the headers Name, Email, Department, and Amount in cells A1:D1.
Keep labels beside their matching inputs and arrange the buttons consistently. A sensible tab order lets a person move through fields with Tab instead of clicking each one. You can adjust tab order in the form designer, and test it after changing the layout.
Write the Save, Clear, and Cancel Code
Double-click the form background to open its code module. Add the following code there, not in a worksheet module or a standard module. It assumes the control names and worksheet layout above. The department choices are examples; replace them with options that fit the workbook.
Option Explicit
Private Sub UserForm_Initialize()
' Load the choices each time the form opens.
With Me.cboDepartment
.AddItem "Finance"
.AddItem "Operations"
.AddItem "Sales"
End With
End Sub
Private Sub btnSave_Click()
Dim ws As Worksheet
Dim nextRow As Long
Dim amountValue As Double
Dim hasAmount As Boolean
' Check required entries before writing a row.
If Len(Trim$(Me.txtName.Value)) = 0 Then
MsgBox "Enter a name before saving.", vbExclamation
Me.txtName.SetFocus
Exit Sub
End If
If Len(Trim$(Me.txtEmail.Value)) = 0 Then
MsgBox "Enter an email address before saving.", vbExclamation
Me.txtEmail.SetFocus
Exit Sub
End If
If Me.cboDepartment.ListIndex = -1 Then
MsgBox "Choose a department before saving.", vbExclamation
Me.cboDepartment.SetFocus
Exit Sub
End If
' Allow the amount to be blank, but reject nonnumeric text.
hasAmount = (Len(Trim$(Me.txtAmount.Value)) > 0)
If hasAmount Then
If Not IsNumeric(Me.txtAmount.Value) Then
MsgBox "Enter a numeric amount or leave the field blank.", vbExclamation
Me.txtAmount.SetFocus
Exit Sub
End If
amountValue = CDbl(Me.txtAmount.Value)
End If
' Records must exist and have headers in row 1.
Set ws = ThisWorkbook.Worksheets("Records")
nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
ws.Cells(nextRow, 1).Value = Trim$(Me.txtName.Value)
ws.Cells(nextRow, 2).Value = Trim$(Me.txtEmail.Value)
ws.Cells(nextRow, 3).Value = Me.cboDepartment.Value
If hasAmount Then
ws.Cells(nextRow, 4).Value = amountValue
Else
ws.Cells(nextRow, 4).ClearContents
End If
MsgBox "Record saved.", vbInformation
ClearForm
End Sub
Private Sub btnClear_Click()
ClearForm
End Sub
Private Sub btnCancel_Click()
Unload Me
End Sub
Private Sub ClearForm()
Me.txtName.Value = vbNullString
Me.txtEmail.Value = vbNullString
Me.cboDepartment.ListIndex = -1
Me.txtAmount.Value = vbNullString
Me.txtName.SetFocus
End Sub
The save procedure finds the next available row by looking down column A, which is why the name field is written there and the header row must be present. It writes the four values in the same order as the headers. The amount field is optional; if it contains text, IsNumeric checks whether VBA can interpret it as a number, and CDbl converts it before writing. Number interpretation can depend on regional settings, so test decimal and thousands separators in the environment where the workbook will be used.
The email check here only requires a nonblank entry; it does not verify that an address is deliverable or fully valid. More elaborate checks are possible, but simple validation should not be presented as proof that contact details are correct. Likewise, if the Records sheet is missing or renamed, the save procedure will raise a VBA error. Create that sheet with the expected name, or add explicit error handling if users may change workbook structure.
Open the Form from a Workbook
To show the form during development, click inside a procedure in its code module and press F5. For a normal workbook workflow, add a macro in a standard module and assign that macro to a worksheet button or another suitable trigger. In the VBA editor, choose Insert > Module, then add:
Option Explicit
Public Sub OpenEntryForm()
' Display the data-entry form.
frmEntry.Show
End Sub
To assign it to a worksheet button, return to Excel, choose Developer > Insert > Button (Form Control), draw the button, and select OpenEntryForm when prompted. This is a Form Control button, not an ActiveX control. A shape can also be assigned a macro through its context menu. Use clear button text and consider putting brief instructions near the form trigger.
Test the Form and Protect the Workbook
Before using the form for real records, test normal and incorrect entries in a copy of the workbook. Confirm that each saved value lands under the correct header, that a blank required field stops the save, that an unselected department is rejected, and that a blank amount stays blank. Also check that Clear empties the controls and Cancel closes the form without adding a row.
Save the file as .xlsm; an ordinary .xlsx file cannot store VBA macros. Excel may show a security warning when a macro-enabled workbook is opened. Only enable macros in files you trust and understand. VBA code can change workbook data, files, and other settings, so macro security is a real consideration, not just a setup hurdle. Keep an untouched backup before running unfamiliar code. Macros cannot be undone with Ctrl+Z, and saving after an unintended change can make recovery harder.
Common Problems and Practical Fixes
- “Subscript out of range” on save: check that the worksheet is named exactly
Recordsand belongs to the workbook containing the form. Spelling and spaces matter. - “Method or data member not found” while editing: compare every control name in the code with the control’s
(Name)property. A caption such as Save is not the same as the namebtnSave. - The form opens but the list is empty: confirm that the initialization procedure is named
UserForm_Initializeand that the combo box is namedcboDepartment. - Values appear in the wrong columns: compare the worksheet headers, cell assignments, and expected data order. The example writes name, email, department, and amount to columns A through D.
- The macro will not run: check that the workbook is saved as
.xlsm, that macros are permitted under the organization’s security policy, and that the button is assigned to the correct macro.
When debugging, use the Visual Basic Editor’s Debug > Compile VBAProject command to catch some compile-time issues, then step through code with F8 if needed. Compilation will not detect every runtime problem, such as a missing worksheet. Read the error message and inspect the highlighted line instead of suppressing errors indiscriminately with broad On Error Resume Next statements.
When a UserForm Is the Right Choice
A UserForm is a good fit when a workbook needs a custom Windows desktop input window, event-driven behavior, or a guided sequence of fields. It may be excessive for a small list that can be entered directly into a well-structured Excel table with data validation. A table is often easier to filter, extend, and share, while a form gives more control over the entry experience.
VBA UserForms are not available in Excel for the web, which cannot create or run VBA macros. Mac users can use VBA in desktop Excel, but menus, keyboard shortcuts, and some interface details differ from Windows; the Windows-specific Developer and editor paths above may not match exactly. Test any VBA workbook on the intended Mac or Windows version before distributing it. If users need browser-based entry or shared multi-user data collection, consider a suitable web-based form or application rather than assuming a VBA form will work online.
Frequently Asked Questions
How do I create a UserForm in Excel?
Save a macro-enabled workbook, open the Visual Basic Editor, choose Insert > UserForm, add controls from the Toolbox, and write event code in the form’s code module. On Windows, Alt+F11 opens the editor; the Mac shortcut can differ, and Excel for the web does not support VBA UserForms.
Where should UserForm code go?
Put form-specific events, such as a Save button’s click procedure, in that UserForm’s code module. Put a general macro that opens the form in a standard module. Worksheet-specific event code belongs in the corresponding worksheet module.
Can an Excel UserForm save data to a worksheet?
Yes. VBA can read control values and assign them to worksheet cells, as in the example. The destination sheet and column arrangement must match the code, and the workbook should be saved as a macro-enabled file.
Why does a UserForm macro work on Windows but not Excel for the web?
Excel for the web does not run VBA macros or display VBA UserForms. The workbook can be opened there, but the form workflow needs desktop Excel. Mac desktop Excel supports VBA, although platform differences should be checked and the workbook tested on the target version.
Can I undo a UserForm macro with Ctrl+Z?
No. Excel’s Undo history does not reverse VBA macro actions. Save a backup before running code, test on sample records, and review any write or delete logic before using a macro on important data.
Related
- Remove Duplicates in Excel: Data Tab Steps
- Remove Duplicates in Excel: Shortcut and Safe Steps
- Remove Duplicates by Row in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Value Property: How Cells Read and Set DataIn Excel VBA, Value is a property of a cell or range that reads or sets the data…
- Excel VBA: Check Whether a String Is Empty or BlankIn Excel VBA, use Len(text) = 0 to check whether a string has no characters. If you also…
- VBA Range Object: How to Work with CellsThe Excel VBA Range object represents one or more cells on a worksheet, letting code read or change…
- Excel VBA: Add a New Worksheet with a Specific NameTo add a worksheet and give it a name with Excel VBA, add the sheet, then set its…
- Excel VBA Loop Statements: Types, Examples, and TipsAn Excel VBA loop statement repeats a block of code, either for a set number of times or…
- VBA InputBox Integer: Get and Validate Whole NumbersVBA’s standard InputBox returns text, not an Integer. In Excel VBA, use Application.InputBox with Type:=1 to request a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.