UserForm VBA properties control a form’s identity, appearance, size, position, and behavior in an Excel desktop workbook. You can edit many of them in the Visual Basic Editor’s Properties window or set them with VBA code; the form’s (Name) identifies it in code, while Caption is the text users see in its title bar.
What UserForm properties are
A UserForm is a custom window you create in the VBA editor to collect input, present choices, or display information. Its properties are settings that describe how the form or one of its controls looks and behaves. A text box, for example, has properties such as Value, Width, and Enabled; a command button has properties such as Caption and Default.
Properties are different from methods and events. A property stores or returns a setting, a method performs an action, and an event runs code when something happens. For instance, Caption is a property, Show is a method that displays a form, and Initialize is an event that runs as the form is prepared. Knowing the distinction helps you find the right place to make a change.
Properties can be set before a form is shown, while it is being used, or both. Some are intended mainly for design-time configuration, and a few are read-only at run time. The Properties window indicates available settings for the selected object; consult its displayed options rather than assuming every property can be changed in code.
Where to find the Properties window
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, select a UserForm or one of its controls in the Project Explorer or form designer, then press F4 to show the Properties window. If the form is not open, find it under the workbook’s Forms folder in Project Explorer and double-click it.
The selected object matters. Selecting the form displays form-level properties; selecting a text box or button displays that control’s properties. The dropdown at the top of the Properties window also lets you select an object on the current form. Properties may be shown alphabetically or by category, depending on the window’s view buttons, so the same setting might appear in a different location.
For a visual change, edit the value in the Properties window and inspect the form designer. For a change that depends on data or user actions, write VBA in the form’s code module or another suitable module. Excel for the web does not run VBA or display VBA UserForms. Excel for Mac has a VBA editor, but particular controls, operating-system integrations, and interface details may differ from Windows.
Main groups of UserForm properties
Most useful properties fall into a few practical groups. The exact list varies by selected object, so a form’s properties are not identical to a button’s or text box’s.
- Identity:
(Name)is the VBA identifier used to refer to the form or control. A text box namedtxtEmailcan be referenced asMe.txtEmailinside the form’s code. - Text and appearance:
Caption,Font,BackColor, andForeColoraffect visible text and colors where supported. - Size and position:
Width,Height,Left, andTopdefine dimensions and placement. A form’sStartUpPositioninfluences where it opens. - Interaction:
Enabled,TabIndex, and control-specific settings affect whether and how a user can interact with an object. - Window behavior:
BorderStyle,ControlBox, andShowModalaffect the form’s frame, title-bar controls, or whether the user can work elsewhere while it is open. - Extra information:
Tagprovides a place to store a small associated value, and help-related properties can connect a form with help resources.
These examples are a guide, not a promise that every property appears for every version or object. Use the object selector and Properties window to confirm the settings exposed for the form you are editing.
Name, Caption, and the difference between them
The distinction between (Name) and Caption is one of the most important UserForm basics. Suppose the form’s (Name) is frmOrder and its Caption is Enter an order. Code refers to the form by its identifier, while the user sees the friendly title in the window’s title bar. Changing the caption does not change the identifier used in VBA.
Control names work the same way. You might name a text box txtQuantity and set its caption nowhere at all; the user sees the text box, not its code name. A label beside it can have a Caption such as Quantity. Descriptive prefixes are optional, but clear names make code easier to follow, especially when a form has many controls.
Changing (Name) after writing code can break references that use the old name. If you rename a control, update event procedures and every reference to it. The event procedure name is tied to the control’s name and event, so renaming through the Properties window may require checking that the expected event code remains associated with the renamed control.
Appearance, size, and position
Use Width and Height to size the form and controls, and Left and Top to position them. In the designer, these measurements help align elements precisely. At run time, they can be adjusted before showing the form, but hard-coded coordinates may not look equally good on every display or with every Windows scaling setting.
StartUpPosition controls the form’s initial placement. Common choices include centering relative to its owner, centering on the screen, using a manual position, or letting Windows choose. When you select manual positioning, set Left and Top deliberately. A form that opens partially off-screen can be difficult to recover, so test placement on the displays and scaling arrangements your users are likely to use.
Properties such as BackColor, ForeColor, and Font can improve visual clarity, but consistency is more useful than decoration. Maintain readable contrast and avoid shrinking text simply to fit a crowded form. If a form needs more room, consider rearranging fields, using a multipage control, or enabling scrolling rather than making every control smaller.
BorderStyle determines whether the window has a border and what kind. The available choices affect resizing and the form’s appearance. ControlBox governs whether the title-bar control box is displayed. These settings can affect how users close or manage the form, so preserve a clear way to leave the screen even if you remove familiar window controls.
Behavior and opening a form
A modal form keeps the user focused on that form until it is dismissed; a modeless form allows work elsewhere in Excel while it remains open. A form’s ShowModal setting describes modal behavior in the form’s design-time properties. In code, the Show method can also be called with vbModal or vbModeless to specify how that call displays the form. Avoid opening a second copy unintentionally when a modeless form is already available.
Forms also have events that provide natural points for setting properties. UserForm_Initialize runs as a form instance is initialized, making it a common place to set an initial caption, clear fields, or populate a list. Activate runs when the form becomes active, which can happen again if the user returns to it. Use initialization for one-time setup and activation only for work that genuinely needs to happen when the form becomes active.
For example, place this event procedure in the code module for a form that contains a text box named txtName. It sets two properties before the form is shown:
Private Sub UserForm_Initialize()
Dim openingCaption As String
' Set the title and prepare the input field.
openingCaption = "Customer details"
Me.Caption = openingCaption
Me.txtName.Value = vbNullString
End Sub
Here Me means the current form instance. The example changes the form’s Caption property and the text box’s Value property. If your control has a different name, replace txtName with its actual (Name).
Setting properties with VBA
In code, a property assignment generally takes the form object.Property = value. You can set values in the Properties window when they are fixed, or assign them in code when they should vary with the workbook’s data or the user’s choices. For instance, set a label’s caption from a worksheet value, or disable a button until required fields are complete.
This procedure belongs in a standard module and opens a form instance modally. Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.
Public Sub OpenCustomerForm()
Dim customerForm As UserForm1
' Create an instance, set a property, then show it.
Set customerForm = New UserForm1
customerForm.Caption = "Customer details"
customerForm.Show vbModal
Set customerForm = Nothing
End Sub
Replace UserForm1 with the form’s actual VBA name. Creating an explicit instance is useful when you want to work with a particular form object. Excel VBA also supports a form’s default instance, but mixing default instances and explicitly created instances can make it harder to tell which form a line of code is changing.
For a control on the current form, use Me, as in Me.Caption or Me.txtName.Value. From another module, refer to the particular form instance you created. This distinction matters when code sets a property: changing one instance does not necessarily change a different instance of the same form.
Common problems and practical checks
- The title bar does not change: Check that you changed
Caption, not(Name), and that the code targets the form instance currently on screen. - Code says a member is not found: Check spelling and the selected object’s actual
(Name). A property available on a form may not exist on a particular control. - The form appears in the wrong place: Review
StartUpPosition,Left, andTop, and test on the intended display setup. - A control cannot be edited: Inspect its
Enabledproperty and any code that changes it. Also check whether the form is modal and waiting for user input. - A renamed control stops responding: Search the form and project for its old name, then verify the control’s event procedures and code references.
- Nothing changes after editing a property: The selected object may be the wrong one, or initialization code may overwrite the design-time value. Check event code that assigns to the same property.
When diagnosing a visual issue, select the exact object in the designer, confirm its name in the Properties window, and then search the code for assignments to the property in question. A value set in an event procedure can replace the value you entered in the designer as soon as the form initializes.
Properties window or VBA code?
Use the Properties window for stable choices that should be visible to anyone maintaining the form: control names, layout, labels, colors, and initial design settings. It is quick and reduces code for values that never need to change. It also makes the form’s structure easier to inspect in the designer.
Use VBA assignments when a setting depends on conditions. For example, code can display a different caption for a new record and an existing record, or disable a button when validation fails. Keep such logic near the event or procedure that explains why the property changes, and avoid assigning the same setting in several unrelated places.
Many forms use both approaches: the designer establishes a sensible default layout, and initialization code fills in data-dependent values. This makes the form usable even before every run-time condition has been handled, while keeping dynamic behavior in code.
Frequently Asked Questions
What is the difference between a UserForm’s Name and Caption?
The (Name) is its identifier in VBA code. Caption is the visible title text shown to the user. Changing one does not automatically change the other.
How do I open the UserForm Properties window?
In the Visual Basic Editor, select the form or control and press F4. The window shows properties for the currently selected object.
Can I change a UserForm property while the form is open?
Many properties can be assigned at run time, including a form’s caption. Some properties are design-time only or read-only at run time, so check the property’s availability for the selected object.
How do I make a UserForm open in the center of the screen?
Set the form’s StartUpPosition property to the screen-centering option in the Properties window. The available numeric values are listed with the property; verify the selected option rather than guessing its value.
Do UserForms work in Excel for the web?
No. Excel for the web does not run VBA macros or VBA UserForms. Use desktop Excel for a workbook that depends on them, or consider a non-VBA interface for browser-based use.
Related
- Excel Data Validation Icon: What It Looks Like and Does
- Data Validation Multiple Selection in Excel: What Works
- Data Validation Interview Questions and Strong Answers
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Location: Where It Appears and How to Control ItA VBA MsgBox has no built-in argument for setting its screen coordinates. In Excel for Microsoft 365 on…
- Excel Macro Goal Seek: Automate a Target-Value SearchAn Excel Goal Seek macro uses VBA’s Range.GoalSeek method to make a formula cell reach a target by…
- Excel VBA Active Worksheet: How It Works and When to Use ItIn Excel VBA, ActiveSheet refers to the sheet currently selected in the active workbook. It is not a…
- Macro Recorder Compatibility Across Excel VersionsExcel’s macro recorder creates VBA macros that work in desktop Excel for Windows and Mac, but compatibility is…
- VBA Add Leading Zeros to a String Without Losing TextIn VBA, add leading zeros to a string by placing the required number of zeros before its existing…
- Loop Through a Named Range in VBA: Practical ExamplesTo loop through every cell in an Excel named range in VBA, assign the range to a Range…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.