UserForms in Excel VBA – UserForm Basics; Add Controls dynamically at run-time using the Add Method; UserForm Controls (A Snapshot)
———————————————————————–
Contents:
UserForm Controls – A SnapShot
———————————————————————–
Note: In below given examples, vba codes are required to be entered in the Code Module of the UserForm, unless specified otherwise.
UserForm is a customized user interface, developed in VBA. It enables a user to interact, using a Form, in an organized and logical manner, to make data entry or for data retrieval with an Excel worksheet or to run a VBA application. UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless controls are added to it which are the actual user-interactive objects.
ActiveX controls can be used with VBA code or without, either directly on worksheets, while some can be used only on VBA UserForms. Using ActiveX Controls on a Worksheet have been illustrated in detail, in the separate section of “Excel VBA: ActiveX Controls, Form Controls & AutoShapes on a Worksheet“.
Open Visual Basic Editor (VBE) in Excel by pressing ALT+F11 in an open workbook, or right-click on the worksheet tab at the bottom, and select View Code.
Click on View -> Project Explorer if the Project window is not visible.
Create a Userform in the Project window of the Visual Basic Editor by clicking Insert -> UserForm, and your screen should appear as shown in Image 1.
If the Toolbox does not appear by itself, first check if it is not hidden by clicking on the UserForm (or double-clicking on UserForm name in Project Window), else click View -> Toolbox (while UserForm is selected);
Toolbox consists of Controls required for developing a VBA Application. Controls are added in the UserForm from the Toolbox, in the Visual Basic Editor.
If the Properties Window is not visible, click on View -> Properties Window. On clicking the UserForm in the Project Explorer, you can view and change the properties of the UserForm in the Properties Window. Each control in the Toolbox such as a Label or TextBox, has its own associated properties, which can be edited in the Properties Window (ensure that the respective control is selected).
UserForm Controls – A SnapShot
UserForm Controls ToolBox is shown in Image 2
UserForm ToolBox Controls – A Snapshot
| Control | Use |
| Label | Stores text which is not editable by user, and is used to describe other controls. |
| TextBox | Holds text by allowing user to enter or modify. |
| ComboBox | Is a list of items from which a user can select, and also allows user to enter his own item in the text box. ComboBox is a combination of TextBox and ListBox. |
| ListBox | Is a list of items from which a user can select, but does not allow user to enter his own item. |
| CheckBox | A CheckBox whether selected or not, indicates True or False values. Used to select mutually exclusive options viz. Yes/No, On/Off, … |
| OptionButton | Used to make one selection from multiple options. Also referred to as Radio Button. |
| ToggleButton | Executes one action when clicked first and a different action on the second click. The button toggles bewteen the two states, On and Off. |
| Frame | Used to group controls that work together or have some commonality. Particularly useful to group OptionButtons which become mutually exclusive within a Frame. |
| CommandButton | A button, when clicked by the user, executes a code or action. |
| TabStrip | Is a collection of Tabs wherein each Tab contains the same set of controls, and the content of the controls changes when a different Tab is selected. |
| MultiPage | Comprises of one or more Page objects, each containing its own set of controls. Selecting a Page (ie. making it visible) hides the other Pages of the MultiPage control. |
| ScrollBar | Is used to change (increment or decrement) the value displayed by other controls. The ScrollBar box can be dragged to change the control’s value over larger increments. |
| SpinButton | Similar to a ScrollBar, is used to increment or decrement the value displayed by other controls, without the drag facility. |
| Image | Is used to display a Picture on the UserForm. |
| RefEdit | Allows a user to select a worksheet range from a box, or to type in the range therein. It behaves similar to the built-in Excel reference boxes. |
| UserForm Application | VBA Code | What is Does |
| To Display a UserForm | UserForm1.Show | Displays the UserForm with name UserForm1. This code should be inserted in a Standard VBA Module and not in the Code Module of the UserForm. You can create a button in a worksheet, then right click to assign macro to this button, and select the macro which shows the UserForm. |
| Load a UserForm into memory but do not display | Load UserForm1 | Load statement is useful in case of a complex UserForm that you want to load into memory so that it displays quickly on using the Show method, which otherwise might take a longer time to appear. |
| Remove a UserForm from memory / Close UserForm | Unload UserForm1 | Note: The Hide method (UserForm1.Hide) does not unload the UserForm from memory. To unload the UserForm from memory, the Unload method should be used. |
| Unload Me | Use the Me keyword in a procedure in the Code Module of the UserForm. | |
| Hide a UserForm | UserForm1.Hide | Using the Hide method will temporarily hide the UserForm, but will not close it and it will remain loaded in memory. |
| Print a UserForm | UserForm1.PrintForm | The PrintForm method sends the UserForm directly for printing. |
| Display UserForm as Modeless | UserForm1.Show False | If the UserForm is displayed as Modeless, user can continue working in Excel while the UserForm continues to be shown. Omitting the Boolean argument (False or 0) will display the UserForm as Modal, in which case user cannot simultaneously work in Excel. By default UserForm is displayed as Modal. |
| Close a UserForm | Unload UserForm1 | The Unload method closes the specified UserForm. |
| Unload Me | The Unload method closes the UserForm within whose Code Module it resides. | |
| End | Use the End statement in the “Close” CommandButton to close the form. The “End” statement unloads all forms. | |
| Specify UserForm Caption | UserForm1.Caption = “Bio Data” | Caption is the text which describes and identifies a UserForm and will display in the header of the Userform. |
| Set UserForm Size | UserForm1.Height = 350 | Set Height of the UserForm, in points. |
| UserForm1.Width = 550 | Set Width of the UserForm, in points. | |
| Set UserForm Position: | ||
| Left & Top properties | UserForm1.Left = 30 | Distance set is between the form and the Left or Top edge of the window that contains it, in pixels. |
| UserForm1.Top = 50 | ||
| Move method | UserForm1.Move 200, 50 | Move method includes two arguments which are required – the Left distance and the Top distance, in that order. |
To determine which UserForms are Loaded:
Private Sub CommandButton1_Click()
‘return names of all Loaded UserForms:
Dim usrFrm As Object
For Each usrFrm In UserForms
MsgBox usrFrm.Name
Next usrFrm
End Sub
Trapping UserForm Events
Among the most common events used for UserForm and its Controls are the UserForm Initialization event and the Click event. The below example explains how these work.
UserForm Events “Initialize” and “Activate”:
The initialization event precedes activation because the UserForm gets initialized while Loading into memory, and thereafter the form gets activated when it is Shown.
When you “Load” or “Show” the UserForm, the initialization event is the first which fires after the UserForm gets Loaded into memory but before it Shows (ie. displays).
When a UserForm is hidden and you use the Show method, it will fire the activation event but not the initialization event. This means that the Show method will only display the UserForm if already Loaded BUT will also Load the UserForm if not already so.
The initialization event fires only when UserForm Loads into memory, while the activation event fires whenever the UserForm is displayed.
Example of Initialization and Click events:
Private Sub UserForm_Initialize()
‘Set properties of Controls on initialization of UserForm.
Dim i As Integer
‘populate a ListBox
With Me.ListBox1
For i = 1 To 100
.AddItem i & ” yrs”
Next i
End With
‘set size for TextBox
TextBox1.Height = 20
TextBox1.Width = 60
‘set Caption for OptionButtons
OptionButton1.Caption = “Male”
OptionButton2.Caption = “Female”
‘set Caption for CommandButton3
CommandButton3.Caption = “Save”
End Sub
Private Sub CommandButton3_Click()
‘Click Event on clicking the CommandButton3. Note: Caption for CommandButton3 was set as “Save” at initialization of UserForm.
‘saves text entered in TextBox1, in a worksheet range
Sheet9.Range(“A1”).Value = TextBox1.Value
‘saves ListBox selection in a worksheet range
Sheet9.Range(“A2”).Value = ListBox1.Value
‘saves OptionButton selection in a worksheet range
If OptionButton1.Value = True Then
Sheet9.Range(“A3”).Value = “Male”
ElseIf OptionButton2.Value = True Then
Sheet9.Range(“A3”).Value = “Female”
End If
End Sub
In the Visual Basic Editor (VBE):
Controls are added in the UserForm from the Toolbox, in the Visual Basic Editor. Select the control in the ToolBox and drag to the form to add it. Another method is to left-click on the control in the ToolBox, go to the form and left-click again, and the control will be added. You can also resize the control, by pointing the mouse control over its adjustment handles which are in the middle or corner of its side borders, and then drag these handles to adjust to the desired size. You can adjust the control’s alignment and spacing in the UserForm, using the Format menu in VBE.
Using the Add Method (VBA):
In VBA, use the “Add Method” to create or add a control to a Page or UserForm. The Add Method: (i) inserts a new Tab or Page in the controls TabStrip or MultiPage; or (ii) adds controls to a Page or UserForm.
Syntax:
For the controls TabStrip & MultiPage: Set Object = object.Add(Name,Caption,index)
Note 1: Name & Caption are optional in case these are omitted, the defaults are generated.
Note 2: It is also optional to mention index. The position of a tab in TabStrip and of a Page in MultiPage is determined by this wherein index value is 0 for for the first Tab or Page of a collection. Omitting index appends the tab or page at the end of the Tabs or Pages collection.
For other controls: Set Control = object.Add(ProgID, Name,Visible)
Note 1: It is necessary to specify the Programmatic Identifier (ProgID), which identifies an object class. These are given in below Table for all individual Controls.
Note 2: Name and Visible are optional. If name is omitted, the default is generated. Value of Visible is True if the object is visible and False if hidden, default being True.
| Controls | ProgID | Controls | ProgID |
| CheckBox | Forms.CheckBox.1 | MultiPage | Forms.MultiPage.1 |
| ComboBox | Forms.ComboBox.1 | OptionButton | Forms.OptionButton.1 |
| CommandButton | Forms.CommandButton.1 | ScrollBar | Forms.ScrollBar.1 |
| Frame | Forms.Frame.1 | SpinButton | Forms.SpinButton.1 |
| Image | Forms.Image.1 | TabStrip | Forms.TabStrip.1 |
| Label | Forms.Label.1 | TextBox | Forms.TextBox.1 |
| ListBox | Forms.ListBox.1 | ToggleButton | Forms.ToggleButton.1 |
Creating the control TextBox (name: txtSampleTextBox) in vba, using the Add Method:
Dim txtSampleTextBox As MSForms.TextBox
Set txtSampleTextBox = Controls.Add(“Forms.TextBox.1”, “txtSampleTextBox”)
Use the Remove Method to remove a control added using the Add Method (ie. dynamically at run-time). Controls added at design-time cannot be removed.
Syntax (name of the control is CheckBox1): Controls.Remove “CheckBox1”
__________________________________________________________________________________________________
Example: Dynamically add Event Handler Code to a Class Module for Dynamically added Controls (using the Add Method). When controls are added at run-time, you can Trap events in a “WithEvents” class viz. declare a WithEvents variable in a class module. Refer Images 3a (cilcking the CommandButton) and 3b (clicking the CheckBox) . See below codes:
Note: In this example, the TextBox has been created at design-time, while the CommandButton and CheckBox have been added at run-time; the class module named Class1 has been created at design-time wherein the the event handlers for the dynamically added CommandButton and CheckBox reside.
Code to be placed in the Userform module (UserForm8):
Option Explicit
‘Referencing the class module, Class1 – the New keyword instantiates (calls into existence) a class object and assigns the result to a variable named chkB (or cmdB) which references the class. The New keyword creates a new instance of the class by declaring an object variable of type classname which represents the class name:
Dim chkB As New Class1
Dim cmdB As New Class1
—————————————————————————————————————-
Private Sub UserForm_Initialize()
‘Add Controls (CommandButton and CheckBox) on UserForm initialization; trap events in a class:
Dim cmdB1 As MSForms.CommandButton
Dim chkB1 As MSForms.CheckBox
‘add a new CommandButton using the Add Method
Set cmdB1 = Me.Controls.Add(“Forms.CommandButton.1”, “cmdClick”)
cmdB1.Caption = “Click to Enter”: cmdB1.top = 10: cmdB1.Left = 10
‘the event handler (cbEvent1) in re. of the new CommandButton resides in the class module (Class1)
Set cmdB.cbEvent1 = cmdB1
‘add a new CheckBox using the Add Method
Set chkB1 = Controls.Add(“Forms.CheckBox.1”)
chkB1.Name = “chkDemo”: chkB1.Caption = “Check for Msg”: chkB1.top = 50: chkB1.Left = 10
‘the event handler (ckbEvent1) in re. of the new CheckBox resides in the class module (Class1)
Set chkB.ckbEvent1 = chkB1
‘set positional properties for TextBox created at design-time
TextBox1.top = 10
TextBox1.Left = 100
End Sub
Codes to be placed in the in the class module (Class1):
‘Use the WithEvents keyword to define an object variable in a Class module. The WithEvents keyword is valid only in class modules and multiple variables can be defined within a module with this keyword
Public WithEvents ckbEvent1 As MSForms.CheckBox
Public WithEvents cbEvent1 As MSForms.CommandButton
————————————————————————————————————
Private Sub cbEvent1_click()
‘clicking the CommandButton will enter “Hello” in the TextBox and color it yellow – refer Image 3a
‘The class code (entered in the Class Module of “Class1”):
UserForm8.TextBox1.Text = “Hello”
UserForm8.TextBox1.BackColor = RGB(255, 255, 0)
End Sub
—————————————————————————————————————–
Private Sub ckbEvent1_click()
‘selecting the CheckBox will return the message “Yes” – refer Image 3b
‘The class code (entered in the Class Module of “Class1”):
MsgBox “Yes”
End Sub