Menu

Creating User Forms

Other links

Creating User Forms

Pre-defined Dialog Boxes like InputBox and MsgBox functions are useful and quick to use. However, designing your own Dialog Boxes (or User Form), allows you to add other controls and personalise your application.

Creating a new User Form

Make sure you are in the Visual Basic Editor and not Excel.

Select Insert, UserForm:

Note: To load the Properties Window, press the F4 function key.

In addition to the Properties window, when active on the new form, the Toolbox Toolbar automatically appears. (If this is missing, use the View, Toolbox command to show it.)

Also, you may want to display the ‘UserForm’ toolbar to align and rearrange the controls on the form. Select View, Toolbars and choose UserForm.

Userform Toolbar

This toolbar is only available for designing and arranging objects when creating or modifying forms (user forms).

1	Bring to Front moves the selected object to the front of all other objects.
2	Send to Back moves the selected object to the back of all other objects.
3	Group two or more selected objects together as one.
4	Ungroup where a single object was made up of two or more objects.
5	Alignments of selected objects to various alignments - see below.
6	Vert/Horiz Alignments of selected objects - see below.
7	Sizes a number of selected objects to the same dimensions - see below.
8	Zoom the User Form by magnifying/diminishing by percentage.

Alignments (Button 5)

	Choose from one of the alignments as to how a number of selected objects will be placed together.
	This keeps controls on a form or Dialog Box symmetrically aligned and therefore professional looking.
	Objects can also be numerically set using the Properties Window.

Vertical/Horizontal Alignments (Button 6)

These are two repeated types of alignments as mentioned above allowing objects to be centred.

Sizes (Button 7)

Changes the size of selected objects to the same dimension as each other. This can also be set from the Properties Window.

Toolbox Toolbar

A1	Select Objects: When there is no control to draw, this mode allows you to select other controls.
A2	Label: Allows you to create text (caption) that a user does not change.
A3	TextBox: Allows you to create an edit box which a user types into.
A4	ComboBox: Allows the user to select from a drop down box items predefined.
A5	ListBox: As above but shows many item in one view with a vertical scroll bar.
A6	CheckBox: Allows the user to create a CheckBox where an item can only have a yes or no (true or false) answer.
B1	OptionButton: Allows you to display multiple options with a frame where only one can be selected at a time.
B2	ToggleButton: Like a CheckBox, but a button version.
B3	Frame: Allows you to create a frame to store controls in one group (usually option buttons).
B4	CommandButton: Creates a button like the OK, Cancel and Other... Buttons.
B5	TabStrip: Allows you to create multiple pages of the same Dialog Box controls.
B6	MultiPage: Allows you to create multiple pages of different controls (multi- tab Dialog Boxes).
C1	ScrollBar: Provides a graphical scroll bar to allow scrolling through a list of values.
C2	SpinButton: This button allows you to set values by scrolling up or down through ranges.
C3	Image: Allows the user to store graphics in a Dialog Box.
C4	RefEdit: Allows a range to be plotted into this control from a spreadsheet. (Not available in Excel 97).

Adding Controls to a Custom Dialog Box

By using the Toolbox, standard controls (command buttons, text boxes and others) can be added to a user form.

Make sure the form is the active window.

Click on the required control and then click on the user form roughly where the control is to be positioned or drag and drop the control from the Toolbox to the area on the form.

Handlebars appear around the selected control. This allows control(s) to be resized and positioned.

Common Controls

There are many more controls than the standard set which is installed with Microsoft Excel and can be added. Select Tools, Additional Controls....

Tick the required item and choose the OK Button. This updates the Toolbox toolbar.

Creating Tabs for Userforms

If a single user form needs to handle a large number of controls that can be sorted into categories, use a Tab Control.

Tab pages can be added and deleted using the properties of the tab (itself) of the control itself by right-mouse clicking the item.

Setting Controls with Properties (Design Time)

Some controls are better set during the design side of a user form by using the Properties window.

Typical examples of setting these controls:

Sorting out the Tab Order of controls.
Setting the default values to edit boxes, checkboxes and many more.
Creating control tool tips and captions.
Setting the Accelerator key (underscored letter) of a control.
Other colours and graphics that really do not need code handling.

Initialising Controls with Code (Run Time)

Some controls (some included as above) can be set as the userform is running or before the form is displayed by setting properties with code.

Typical examples of setting controls with code:

Setting initial values of controls like an Edit Box or a Combo Box.
Setting the focus of a control.
Validating values in a Dialog Box.
Changing values in a Dialog Box while it is running.
Showing and hiding other controls.
Enabling and disabling controls.

These controls are set to different events of a control. These include:

Click (and double click) of a control - command buttons, checkboxes.
Change of a control - edit boxes.
Initialising of a control - as the form starts up.
Exiting a control.

Example:

Sub MyDialogBox_Initialize()
    TextBox1.Text = "Sales"
    Checkbox1.Enabled = True
    Me.Command1.SetFocus
    OptionCommand1.Visible = False
    Checkbox2.Value = False
End Sub

Five controls are set when the form (MyDialogBox) is shown (Initialised).

TextBox1 displays “Sales” in it.
Chexkbox1 control is active.
Command1 button has the focus.
OptionCommand1 is not visible (hidden).
Checkbox2 is not ticked (False value).

Displaying a Userform

Once the userform has been created, the next stage is to test to see how the user form will look. You can use the Run (F5 function key in design time mode) command when the active form is displayed. But, writing code is ultimately how a user form will be used.

Decide where the code is to be stored (in a Module, Worksheet or Workbook).

Use the name of the form with the Show method command.

Example:

Sub DisplayMyUserform()
MyUserForm.Show
End Sub

The user form is known, as ‘MyUserForm’ and the Show method will display the user form.

There is one optional argument called Modal which can be explicitly defined and has a value of 0 or 1 .

1 = a modal state which means that users have to complete the form and can not click anywhere else (in the background).

0 = a modeless state which allows users to click outside the form area.

The default is 1 if omitted.

MyUserForm.Show 0

MyUserForm.Show 1 or MyUserForm.Show

Adding Code to respond with User Forms

Each control will have its own set of events. These events store the code and are executed when that event is triggered.

For example, a Button recognises the Click_Event, a Form recognises an Intitialize_Event and a Combo Box recognises a Change_Event.

To assign code to a control, display the form and double click on that control. This opens the module and the main event allowing code to be written:

Example:

When the OK Button is clicked...

Private Sub cmdOK_Click()
    Range("NameResult").Select
    EnterText.Hide
    If txtName.Text = "" Then
        MsgBox "Must enter a name. Try again."
        EnterText.Show
    End If
    ActiveCell.Value = txtName
    Unload Me
End Sub

When the userform is displayed, if the OK Button is clicked, the above code is executed and checks to see if this Textbox (txtName) is empty or not using the If statement. If false, it displays a message prompt and shows the user form again. If true, it enters the data into a spreadsheet (range - NameResult).

Unload Me is the way to close a form (itself)

The Me Property

The Me property returns a reference of the form itself that the code is currently running. This is used as shorthand for the full reference of a form.

Example:

Suppose you have the following procedure in a module:

Sub ChangeFormColour(FormName As Form)
FormName.BackColor = RGB(Rnd * 256, Rnd * 256, Rnd * 256)
End Sub

You can call this procedure and pass the current instance of the Form as an argument using the following statement:

Sub cmdColour_Click()
ChangeFormColour Me
End Sub

The ChangeFormColour procedure is passed to the Me property in the current form running which therefore changes the colour of a specified control(s) to the colour defined.

RGB(Rnd * 256, Rnd * 256, Rnd * 256) is a Red, Green and Blue colour function.

To see an example, click on userform example

Next Topic: DAO/ADO Objects

Want to teach yourself Access? Free online guide at About Access Databases

Creating User Forms

Creating a new User Form

Userform Toolbar

Toolbox Toolbar

Creating Tabs for Userforms

Setting Controls with Properties (Design Time)

Initialising Controls with Code (Run Time)

Displaying a Userform

Adding Code to respond with User Forms

The Me Property

Tip: Press the F5 function key to run and preview a form during the design time environment.

VBA Keywords: Show, If...Then...Else, MsgBox, RGB & Unload.