VBA UserForms

A UserForm is a custom window that can contain various types of controls which can be used to provide an intuitive graphical interface to users.

Create UserForm

To create a UserForm right-click in the VBA Project Explorer and select insert UserForm or import the UserForm from a file.

Launching and Closing UserForm

When launching or closing a UserForm, there is a difference between loading the UserForm into memory and showing the UserForm to the user, and between hiding a UserForm and unloading it from memory.

Load and Show UserForm

Loading the UserForm into memory triggers the UserForm's Initialize event procedure. To load a UserForm into memory use the Load statement. To show a UserForm use the Show method of the UserForm object. If the Show method is called on a UserForm that is not loaded in memory, it will be loaded automatically and the Initialize event of the UserForm will be triggered. The Show method takes an optional argument, a member of the FormShowConstants enum (vbModal or vbModeless), to specify the UserForm's modality. Modal (the default) means the user will not be able to interact with the rest of the application while the UserForm is open. Modeless means the user can interact with the application while the UserForm is open.

Public Sub LoadForm()
    
    'UserForm is loaded into memory and the Initialize event procedure is triggered
    Load UserForm1
    
    'UserForm is Displayed
    UserForm1.Show vbModal

End Sub

Hide and Unload UserForm

Once a UserForm is open, it can be either hidden or unloaded. When a UserForm is unloaded from memory, the Terminate event procedure is triggered. Hiding a UserForm does not unload the UserForm from memory nor does it trigger the Terminate event. To hide a UserForm call the Hide method on the UserForm object. To unload a UserForm from memory use the Unload statement. Manually closing the UserForm will also unload the UserForm from memory and trigger the Terminate event.

Public Sub UnloadForm()

    'UserForm is Hidden
    UserForm1.Hide 
    
    'UserForm is unloaded from memory and the Terminate event procedure is triggered
    Unload UserForm1 

End Sub

Initialize and Terminate Events

The Initialize event of a UserForm is often used to set properties, values, positions and data sources for controls before the form is displayed. The terminate event can be used to free resources and close connections to files or databases. To add an Initialize or Terminate event procedure to a UserForm use the object box and procedure box drop-downs to select the event procedures in the UserForm's code window.

UserForm Controls

UserForms have a variety of available controls which can provide functionality to a form.

Basic Controls

Label
TextBox
ComboBox
ListBox
CheckBox
OptionButton
ToggleButton
Frame
CommandButton
TabStrip
MultiPage
ScrollBar
SpinButton
Image

Additional Controls

ImageCombo
ImageList
InkEdit
InkPicture
ListView
ProgressBar
RefEdit
Slider
StatusBar
TabStrip
Toolbar
TreeView
Windows Media Player

Adding Controls

Controls can be added to a UserForm using the Toolbox. To add a control you can either select the control and draw it on the form, drag the control to the form, or double-click the control and then click the form. Right-Click on the Toolbox to access additional controls.

UserForm Toolbox Right Click Additional Controls

Programmatically Add Controls

Controls can be added programmatically using the Add method of a UserForm, Tab, or Page. When programmatically adding a control to a UserForm, a member variable for the control needs to be created in the UserForm code module which is declared using the WithEvents keyword.

'UserForm: UserForm1

Option Explicit

Private WithEvents ExampleButton As MSForms.CommandButton

Private Sub UserForm_Initialize()

    Set ExampleButton = Me.Controls.Add("Forms.CommandButton.1", "ExampleButton")
    ExampleButton.Caption = "Example"

End Sub

Private Sub ExampleButton_Click()

    MsgBox "Hello, World!"

End Sub

Adding Code To Controls

To add code to a control which is added via the visual editor, right-click the control and select View Code. Use the procedure box drop-down to add event procedures for a control or form. To add code to a control which is added programmatically, declare the control as a member variable of the UserForm and then select its events using the object-box and procedure-box drop-downs.

Properties

Selecting a UserForm or its controls will show their properties in the Properties Window. Most often it is better to set the properties of a UserForm and its controls programmatically in the Initialize event of the UserForm. However, the UserForm's Name and size are usually set in the Visual Basic Editor in the Properties window and by resizing the form object visually in the Editor.

Tab Order

Tab order is the order that controls will be cycled through using the tab key. To edit the tab order right-click on the UserForm and select Tab Order or select Tab Order from the view menu. Move controls up or down using the popup Tab Order window.

Tab order can also be set programmatically by setting the TabIndex property of the controls. The TabStop property can also be set to allow or prevent tab from focusing on an object.

Option Explicit

Private Sub UserForm_Initialize()

    Me.Label1.TabIndex = 1
    Me.Label1.TabStop = False 'TabStop = False is default for Labels anyway
    
    Me.CommandButton1.TabIndex = 2
    
    Me.CheckBox1.TabIndex = 3
    
    Me.CheckBox2.TabIndex = 4
    
    Me.TextBox1.TabIndex = 5

End Sub