Collection of User Form Controls

When you have a number of the same type of control on a form, you often write almost identical event procedures for each one. For example, say you want to be able to double-click the label to the left of each of the TextBox in the UserForm in Figure 16-6 to clear the TextBox and set the focus to the TextBox. You would normally write four almost identical event procedures, one for each label control.

Fruit Prices





OK |


Cancel |

Figure 16-6

Using a class module, you can write a single generic event procedure to apply to all the label controls, or just those that need the procedure. The label controls and TextBox in the UserForm have been given corresponding names, as follows:











The following code is entered in a class module CControlEvents:

Public WithEvents lbl As MSForms.Label Public frm As UserForm

Public WithEvents lbl As MSForms.Label Public frm As UserForm

Private Sub lbl_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim sProduct As String Dim sTextBoxName As String

'Get product name from label caption sProduct = lbl.Caption

'Construct name of associated text box sTextBoxName = "txt" & sProduct

'Assign zero length string and set focus With frm.Controls(sTextBoxName) .Text = ""

.SetFocus End With

End Sub lbl is declared with events as a UserForm label. frm is declared to be a UserForm. The generic DblClick event procedure for lbl gets the product name from the Caption property of the label, and converts this to the TextBox name by appending "txt" in front of the product name.

The With...End With structure identifies the TextBox object by using the TextBox name as an index into the Controls collection of the UserForm. It sets the Text property of the TextBox to a zero-length string and uses the SetFocus method to place the cursor in the TextBox.

The following code is entered into the class module behind the UserForm:

Dim mcolLabels As New Collection

Private Sub UserForm_Initialize() Dim ctl As MSForms.Control Dim clsEvents As CControlEvents

'Loop through all controls on userform For Each ctl In Me.Controls

'Only process labels

If TypeOf ctl Is MSForms.Label Then

'Instantiate class module and assign properties Set clsEvents = New CControlEvents Set clsEvents.lbl = ctl Set clsEvents.frm = Me

'Add instance to collection mcolLabels.Add clsEvents

End If

Next ctl

End Sub mcolLabels is declared as a new collection to hold the objects that will be created from the CControlEvents class module. In the UserForm Initialize event procedure, the label controls are associated with instances of CControlEvents.

The For Each...Next loop processes all the controls on the form. When it identifies a control that is a label, using the TypeOf keyword to identify the control type, it creates a new instance of CControlEvents and assigns it to clsEvents. The lbl property of the new object is assigned a reference to the control, and the frm property is assigned a reference to the UserForm. The new object is then added to the mcolLabels collection.

When the UserForm is loaded into memory, the Initialize event runs and connects the label controls to instances of the class module event procedure. Double-clicking any label clears the TextBox to the right and sets the focus to that TextBox, ready for new data to be typed in.

You need to be aware that some events associated with some controls are not made available in a class module using With Events. For example, the most useful events exposed by UserForm text boxes are BeforeUpdate, AfterUpdate, Enter, and Exit. None of these is available in a class module. You can only handle these events in the class module associated with the UserForm.

+3 -3


Post a comment