Excel VBA

UserForms in Excel VBA - UserForm Basics; Add Controls dynamically at run-time using the Add Method; UserForm Controls (A Snapshot)

User Rating:  / 16


UserForms in Excel VBA - UserForm Basics; Add Controls dynamically at run-time using the Add Method; 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.


Excel VBA UserForms and Controls

1. UserForm in Excel VBA - UserForm Basics; Add Controls dynamically at run-time using the Add Method; UserForm Controls (A Snapshot)

2. UserForm and Controls - Properties.

3. UserForm Controls - Label, TextBox and CommandButton.

4. UserForm Controls - ComboBox and ListBox.

5. UserForm Controls - CheckBox, OptionButton and ToggleButton.

6. UserForm Controls - Frame, MultiPage and TabStrip.

7. UserForm Controls - ScrollBar and SpinButton.

8. UserForm Controls - Image and RefEdit.



Excel UserForms


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 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.



Create a UserForm



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 Basics


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



Add Controls to a UserForm



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.




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



Bottom Ad

© 2014 GlobaliConnect.com. All rights reserved.