Userform Controls

A Userform without controls does not really server any purpose. A Userfrom should at least have a label to provide with some information, otherwise it would be an empty form (unless showing a background picture with message). There are many Userform controls available in Excel. Among these are labels, text boxes, combo boxes, list boxes, check boxes, option buttons, command buttons, and some more. Controls are objects, and therefore, they have properties, methods, and events.

 

Toolbox

Userform controls are added by clicking and/or dragging the control from the toolbox into the form. If the toolbox is not visible, go to “View” tab and click “Toolbox”. The toolbox shows the basic Userform controls.

See below the legend to each control in that picture from left to right.

• Select Object (not a control)
• Label
• TextBox
• ComboBox
• ListBox
• CheckBox
• OptionButton
• ToggleButton
• Frame
• CommandButton
• TabStrip
• Multipage
• ScrollBar
• SpinButton
• Image
• RefEdit

 

Additional controls can be added by right-clicking the toolbox and selecting “Additional controls”. The list is long but many of them may not be available. We can also customize the name and picture for a control in the toolbox (not recommended though).

Control Properties

Most controls have similar properties, which are also similar to Userform properties, for example, the back and fore color properties, border color and style properties, size (Width and Height properties), position in the form (Top and Left properties), font name, size, and style properties, visibility (Visible property), Enable property, etc. Some properties may be specific of a given control though. We will see specific useful properties for some controls in the following chapters. The picture below shows all the properties for a label control in the properties window alphabetic view.

The Name property (or object) represents the name of the Userform control and is used as an object to reference that control, apply properties or methods. The Caption is what we see in the control (only applicable to some controls such as Label). The Value and Text properties get or set the contents for controls that accept input, such as the TextBox, ComboBox, or ListBox. However, there is a slight difference. The Text property returns the current contents of the control while the Value property gets the saved value of the control. The Text property requires the control to have focus. Furthermore, it gets text (string) content, so when empty returns vbNullString, while Value returns Empty. When working with numbers is better to use Value, or have the VBA Function Val to ensure it is a numeric value.

As with Userform properties (see Userform Properties), we can get or set the value for a control property at design-time (in the properties window) or at run-time (with VBA code in either the Userform module or a standard module). The following example uses the List property of the ComboBox control to add a list of RAG status values before the Userform appears.

 Private Sub UserForm_Initialize()
     ComboBox1.List = Array("Green", "Amber", "Red")
 End Sub

 

This other example gets the value in a text box when submitting the form by clicking a button, and calls another macro that will do something with that input.

 Private Sub CommandButton1_Click()
     userInput = TextBox1.Value
     Call otherMacro(userInput)
 End Sub

 

Control Methods

Some controls have methods to execute an action linked to the control. There are not many though. The ComboBox and ListBox controls have for example the AddItem and RemoveItem methods to add and remove entries in the combo or list box. We will see how to use those in the following chapters. The code below updates the possible values for RAG status and adds a new entry in a combo box (from example above) when clicking a button (CommandButton1).

 Private Sub CommandButton1_Click()
     ComboBox1.AddItem "New status"
 End Sub

 

Many controls have a SetFocus method, which somehow prioritizes the focus on that control (for example on a text box that expects some input). For example, if we want to make sure the user is prompted to a given box to provide input, we could use SetFocus to place the cursor in that box before the Userforms opens (We could also use the TabIndex property for this purpose).

 Private Sub UserForm_Initialize()
     TextBox1.SetFocus
 End Sub

 

Control Events

All the Userform controls have events that can be recognized and programmed. Many of the events are common to most (if not all) the controls (and also the Userform). Some events may be specific of a given control though. We can see the list of events for a given control (when added to the Userform) in the drop-down on the top-right of the code window when selecting the control in the drop-down on the left.

Click and DblClick events, mouse-related events (MouseUp, MouseDown, MouseMove), key-stroke related events (KeyDown, KeyPress, KeyUp), are events common to most Userform controls (with some exceptions). Change, BeforeUpdate, AfterUpdate events are usually associated with controls that accept input such as text, combo, or list boxes.

The code to respond to an event is stored in the corresponding event procedure. We can add an event procedure with VBA code as done with Userform events (see Userform Events). Here are some examples of the most common events and code used, but we will see more specific examples in the following chapters.

The code below closes the Userform upon clicking a button. The name of the button has not been changed; it is CommandButton1 as per default.

 Private Sub CommandButton1_Click()
     Unload Me
 End Sub

 

This other example reacts when selecting a value in a combo box and performs the corresponding action. For example, when the RAG status is red, it prompts a message and then calls another macro to take action. The code is added to the event procedure just for demonstration purposes. However, it should rather go into a macro in a standard module, being called from the event procedure in the Userform module.

 Private Sub ComboBox1_Change()
 
     RAGstatus = ComboBox1.Value
 
     If RAGstatus = "Red" Then
         MsgBox "Red project - prompt action"
         'call macro to execute action
     ElseIf RAGstatus = "Amber" Then
         MsgBox "Amber project - keep on watch"
     Else
         'do nothing
     End If
 
 End Sub

 

 <<< Previous | Next >>>