To add controls to a VBA UserForm, open the form in the Visual Basic Editor and drag a control from the Toolbox onto it; to create controls while the form runs, use the form’s Controls.Add method in VBA. The design-time method is simplest for a fixed layout, while runtime creation is useful when the controls or their number depend on data.
What UserForm Controls Do
A UserForm is a custom dialog window built with Visual Basic for Applications (VBA). Controls are the interactive or informational objects placed on that window: a TextBox accepts input, a Label displays instructions, a CommandButton starts an action, and a ListBox presents choices, for example. The form and its controls are part of the workbook’s VBA project; they are not worksheet cells or controls placed directly on a sheet.
In Excel for Microsoft 365 on Windows, you work with UserForms in the Visual Basic Editor (VBE). Press Alt+F11 in desktop Excel to open it, then choose Insert > UserForm to create a form. Excel for the web cannot create or run VBA macros, so a workbook containing a UserForm must be opened in a desktop version of Excel to use it.
Add Controls in the Visual Basic Editor
For a form whose fields are known in advance, add controls at design time. This lets you position and configure them visually and then write event procedures for them. To get started:
- In the VBE, choose Insert > UserForm, or select an existing form in Project Explorer and open it.
- Display the Toolbox if it is not visible, using View > Toolbox.
- Select a control, such as TextBox or CommandButton, and drag on the form to draw it. You can also select the control and click the form to add it.
- Open the Properties window with
F4. Set(Name)to a meaningful identifier, and set properties such asCaption,Width, andHeightas appropriate. - Repeat for the other controls, then arrange them and check the form in its running state.
The Properties window is where you configure a selected object; it is not the same as the Toolbox, which supplies objects to place. A Label’s Caption is its displayed text, whereas a TextBox’s Value is typically the text entered by a user. Naming controls clearly, such as txtCustomer or cmdSave, makes the code easier to read than relying on default names like TextBox1.
Choose Controls for the Task
Choose a control based on the interaction the form needs, rather than adding controls simply because they are available. Common choices include:
- Label: shows a prompt, heading, or explanation. It does not normally accept typed input.
- TextBox: accepts or displays text. Use a separate Label to explain what belongs in the box.
- CommandButton: performs an action, such as submitting or cancelling a form.
- ComboBox: offers a drop-down list and may allow typing, depending on its settings.
- ListBox: shows one or more selectable items in a list.
- CheckBox or OptionButton: represents a yes/no choice or a selection among alternatives. OptionButtons are commonly grouped in a Frame when the choices are mutually exclusive.
Some controls need extra configuration. For example, a ListBox or ComboBox must receive its choices, either by setting properties or by filling it in code. A control’s visual presence alone does not validate its contents or save anything to the worksheet. The form’s VBA procedures must define what happens when a user interacts with it.
Create Controls While the Form Runs
Use runtime creation when the form’s layout changes according to a user’s selection or when the number of repeated inputs comes from data. The method Controls.Add creates a control and returns a reference that code can use to set its properties. Its first argument is a control’s programmatic identifier, often called its ProgID; for example, a standard Microsoft Forms TextBox uses Forms.TextBox.1.
The following example belongs in the code module for a UserForm. It adds a Label, TextBox, and CommandButton when the form is initialized. Before running a macro, save a copy of the workbook: VBA actions cannot be undone with Ctrl+Z.
Private mButtonEvents As Collection
Private Sub UserForm_Initialize()
Dim lbl As MSForms.Label
Dim txt As MSForms.TextBox
Dim cmd As MSForms.CommandButton
Dim sink As CButtonEvents
' Keep event-handler objects alive for as long as the form is open.
Set mButtonEvents = New Collection
Set lbl = Me.Controls.Add("Forms.Label.1", "lblName", True)
With lbl
.Caption = "Name:"
.Left = 12
.Top = 12
.Width = 60
End With
Set txt = Me.Controls.Add("Forms.TextBox.1", "txtName", True)
With txt
.Left = 78
.Top = 10
.Width = 150
End With
Set cmd = Me.Controls.Add("Forms.CommandButton.1", "cmdSubmit", True)
With cmd
.Caption = "Submit"
.Left = 78
.Top = 42
.Width = 72
End With
Set sink = New CButtonEvents
Set sink.Button = cmd
mButtonEvents.Add sink, cmd.Name
End Sub
Positions and dimensions in this example are measured in points, as used by UserForm layout properties. The third argument to Controls.Add makes the new control visible. The names must be unique within the form; trying to add another control with the same name can cause an error. You can change the sample’s captions, positions, and sizes to suit the form.
Handle Events from Added Controls
Creating a control does not automatically give it a useful action. A control placed at design time can have an event procedure in the form’s code module. Double-click a control in the form designer, or select the control and an event in the code window’s procedure list, to create a procedure such as cmdSubmit_Click. Put the work that should happen after a click in that procedure.
Runtime-created controls require more care. A normal event procedure tied to a control name at design time is not enough for controls that do not exist until the form runs. A class module with a WithEvents variable can receive events from a runtime control. In the example above, add a class module to the VBA project and name it CButtonEvents. Put this code in the class module:
Public WithEvents Button As MSForms.CommandButton
Private Sub Button_Click()
' Respond to a click on the runtime-created button.
MsgBox "Dynamic button clicked.", vbInformation
End Sub
The UserForm code stores each event-handler object in a Collection. That reference matters: if the handler object goes out of scope, it may be released, and its event procedure will no longer handle clicks. For several runtime buttons, create one handler instance per button and keep each instance alive in a collection for as long as needed. If the button needs to read values from the form or identify which item it represents, add an appropriate reference or identifying data to the event-handler class.
Set Layout and Behavior Properties
Whether controls are added visually or through code, properties determine how they appear and behave. The most frequently adjusted layout properties include Left, Top, Width, and Height. Use consistent spacing and alignment so the form remains understandable at different window sizes. For controls users interact with, consider the tab order: TabIndex determines the order in which pressing Tab moves focus.
Other properties affect usability and input. A TextBox’s MultiLine setting allows multiple lines; a ComboBox’s style and list properties influence how choices are presented. Enabled can prevent interaction, while Visible can hide a control until it is needed. Use these properties to communicate the form’s state, not as a substitute for validating submitted data. A user can still provide unexpected or empty values, so check inputs in the button’s event procedure before using them.
For a form with many repeated controls, calculate positions from a common starting point and a fixed spacing value rather than assigning unrelated coordinates. This reduces misalignment and makes it easier to change the layout later. Keep control names predictable too; code that refers to txtName is easier to maintain than code that depends on a sequence of default names whose order may change.
Common Problems and How to Fix Them
- The Toolbox is missing: activate the UserForm designer and choose View > Toolbox. The Toolbox is associated with the form designer, so it may not appear when a code module is active.
- Code cannot find a control: check the control’s
(Name)property and spell the name exactly as it appears in code. A caption is displayed text, not the object name. - A runtime control fails to appear: check the ProgID, form code location, and whether the initialization procedure ran. Also confirm that the control is visible and positioned within the form’s usable area.
- Adding a control raises an error: check for duplicate names and supported control identifiers. Avoid assuming that every optional or third-party control is installed on another computer.
- The button appears but does nothing: verify that an event procedure is connected. For a runtime-created control, confirm that its
WithEventshandler is set and that the handler object remains referenced. - The form looks different on another computer: fonts, display scaling, Office platform, and available controls can affect layout or compatibility. Test the workbook on the systems where it will be used.
When debugging, run the form from the VBE and use the Immediate and Locals windows if needed to inspect values. Compile the VBA project with Debug > Compile VBAProject to catch some coding errors before distributing the workbook. Compilation does not prove the form behaves correctly, so test its controls and input cases as well.
Compatibility and Alternatives
UserForms are VBA features in desktop Excel, not interactive forms in Excel for the web. Excel for Mac supports VBA, but available controls and behavior can differ from Windows; avoid assuming that Windows-specific ActiveX components will work on a Mac. If the workbook must run on more than one platform, favor standard UserForm controls, keep the design simple, and test on each target version and operating system.
A UserForm is appropriate for a guided dialog or a compact data-entry workflow. If users only need to enter a few values, worksheet cells with data validation may be simpler to maintain. For a form that must be available in a browser or outside Excel, consider a web-based form or another supported application instead. These alternatives have different setup and data-storage requirements, so choose based on where users work and how the submitted information should be handled.
Frequently Asked Questions
Can I add a control without using the Toolbox?
Yes. Use Me.Controls.Add in the UserForm’s code to create a control at runtime, then set its properties. You will also need to arrange it and write any required event handling.
How do I add a button to a VBA UserForm?
At design time, choose CommandButton in the Toolbox and draw it on the form. At runtime, add a Forms.CommandButton.1 control with Controls.Add. In either case, write or connect a click event procedure for its action.
What is the difference between a UserForm control and a worksheet control?
A UserForm control belongs to a VBA form window. A worksheet control is placed on a sheet. They have different design surfaces and code patterns; a worksheet ActiveX control is not simply a UserForm control moved onto a sheet.
Can I add controls dynamically based on a list?
Yes. VBA can loop through the list and call Controls.Add for each item, assigning each control a unique name and position. For interactive runtime controls, use event-handler objects and keep them referenced while the form is open.
Why does a dynamically added button not run its Click event?
Runtime controls need an event connection, commonly a class module variable declared with WithEvents. Make sure the variable is assigned to the button and that the class instance is stored somewhere persistent, such as a UserForm-level Collection.
Related
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
- Data Validation and Reconciliation in Excel Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to Create a Pivot Table Using VBA in Excel 365To create a PivotTable with VBA in Excel for Microsoft 365 on Windows, put your source data on…
- VBA Array AutoFilter: Filter Data in Memory or ExcelExcel’s VBA AutoFilter method filters a worksheet range or table; it does not filter a VBA array directly.…
- Excel VBA: What It Is, How It Works, and When to Use ItYes—desktop Microsoft Excel includes VBA, or Visual Basic for Applications, which lets you automate tasks and create macros.…
- Excel VBA Copy an Entire Column: Safe Code and TipsYes. In Excel for Microsoft 365 on Windows, VBA can copy a complete column with a worksheet’s Columns…
- Excel VBA: Delete Visible Rows After AutoFilterTo delete only the rows currently visible in a standard Excel AutoFilter, use VBA to take the filter…
- VBA AutoFilter Method of Range Class Failed?“AutoFilter method of Range class failed” is usually Excel’s run-time error 1004: VBA tried to apply a filter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.