Controls and Collections

In Chapter 20, "Creating Classes, Records, and Collections," several labels on a sheet were grouped together into a collection. With a little more code, these labels were turned into help screens for the users. Userform controls can be grouped into collections, too, and take advantage of class modules.

The following example checks or unchecks all the check boxes on the userform, depending on which label the user chooses.

Place the following code in the class module, clsFormEvents. It consists of one property, chb, and two methods, SelectAll and UnselectAll.

The SelectAll method places a check in a check box by setting its value to True: Option Explicit

Public WithEvents chb As MSForms.CheckBox

Public Sub SelectAll() chb.Value = True End Sub

The UnselectAll method removes the check from the check box.

Public Sub UnselectAll() chb.Value = False End Sub

That sets up the class module. Next, the controls need to be placed in a collection. The following code, placed behind the form, frm_Movies, places the check boxes into a collection. The check boxes are part of a frame, f_Selection, which makes it easier to create the collection:

Option Explicit

Dim col Selection As New Collection

Private Sub UserForm_Initialize() Dim ctl As MSForms.CheckBox Dim chb ctl As clsFormEvents

1 Go thru the members of the frame and add them to the collection For Each ctl In f_Selection.Controls Set chb_ctl = New clsFormEvents Set chb_ctl.chb = ctl col_Selection.Add chb_ctl Next ctl

End Sub

When the form is opened, the controls are placed into the collection. All that's left now is to add the code for labels to select and unselect the check boxes:

Private Sub lbl_SelectAll_Click() Dim ctl As clsFormEvents

For Each ctl In col_Selection ctl.SelectAll Next ctl

End Sub

The following code unselects the check boxes in the collection:

Private Sub lbl_unSelectAll_Click() Dim ctl As clsFormEvents

For Each ctl In col_Selection ctl.Unselectall Next ctl

End Sub

All the check boxes can be checked and unchecked with a single click of the mouse, as shown in Figure 21.2.

0 0

Post a comment