UserForm Event Handler Class – Multiple Controls

UserForm Event Handler Class – Multiple Controls

Down through the ages, VBA programmers have asked, “Do I really need a click event handler for each button on my form, even if they all do the same thing?” The answer, of course, is “no.” You can use a class to create an array of event handlers for the controls. In this post, I’ll expand on that concept to groups of checkboxes that work together in a “group/member” relationship.

checkboxes working together

I’ve been working on a form with groups of checkboxes that perform pretty much the same action. All the checkboxes in a row are controlled by a “group” switch. Conversely, the group switch turns on or off, or goes to that grayed-out “Null” position, based on the state of its “member” checkboxes. Just like this worksheet header/footer preview form. In this case the group switches are the “Headers” and “Footers” checkboxes, with the member checkboxes to the right.

This form uses a collection of classes, one for each of the six member controls. Each class instance contains that single member control, along with a collection of all the member controls in the same row, and the row’s group checkbox. The class contains two event handlers: one for the member checkbox, and one for the group checkbox. To be able to create the event handlers, these two controls are declared using the WithEvents keyword.

The class looks like this:

'clsHeadFooterCheckboxes

Public WithEvents GroupCheckbox As MSForms.CheckBox
Public WithEvents MemberCheckbox As MSForms.CheckBox
Public collmemberCheckboxes As Collection
Public ParentForm As MSForms.UserForm

Private Sub MemberCheckbox_Click()
Dim ctl As MSForms.Control
Dim CheckedCheckboxCount As Long

'Avoid endless control click loops
If MemberCheckbox.Enabled Then
    'count the number of checked "member" controls
    For Each ctl In collMemberCheckboxes
        If ctl = True Then
            CheckedCheckboxCount = CheckedCheckboxCount + 1
        End If
    Next ctl

    With GroupCheckbox
        'Also avoid endless control click loops
        .Enabled = False
        'set the state of the group based on whether
        'all, no, or some members are checked
        .TripleState = False
        Select Case CheckedCheckboxCount
        Case 0
            .Value = False
        Case collmemberCheckboxes.Count
            .Value = True
        Case Else
            .TripleState = True
            .Value = Null
        End Select
        .Enabled = True
    End With
End If
SetTextBoxVisibility

End Sub

Private Sub GroupCheckbox_Click()
Dim ctl As MSForms.Control

'turn members on or off depending on group state
With GroupCheckbox
    'TripleState is only true when set by members
    'We don't want it to be available when clicking group
    .TripleState = False
    For Each ctl In collmemberCheckboxes
        'Avoid endless control click loops
        ctl.Enabled = False
        ctl.Value = .Value
        ctl.Enabled = True
    Next ctl
End With
SetTextBoxVisibility

End Sub

Sub SetTextBoxVisibility()
'Set the textboxes paired to the member controls visibility
ParentForm.Controls(Replace(memberCheckbox.Name, "chk", "txt")).Visible = memberCheckbox.Value
End Sub

While writing this code, I solved a problem that stumped me in the past: how to avoid looping of events when a pair of controls each triggers a change in the other. Application.EnableEvents doesn’t apply to userform controls, so you typically create some kind of EventsEnabled boolean variable. This is easy enough when only one control has a change event, but I’ve never been able to get it to work when two controls are affected by each other’s Change or Click events. This project was even more confusing, because events are triggered in three separate class instances, one for each member control in a row!

My solution was inspired by recent experience with VB.Net, where you can simply add and remove event handlers within your code. If you don’t want to trigger events, just unlink the control from its event handler, and add it back when you’re done. Obviously you can’t do that in VBA, but I realized I could disable a control before performing an action that would normally trigger its event. I did this in the MemberCheckbox_Click event. In the other direction it’s a little different. In the GroupCheckbox_Click event I disable the member checkbox and then check its state in the MemberCheckbox_Click event. This acts like an across-all-class-instances global variable that is tested in the groupCheckbox_Click event. I think. At any rate, it works.

Another tricky part was managing the group checkbox’s TripleState property. It only gets turned on in the MemberCheckbox_Click event, and only when some, but not all, of the member checkboxes are checked. This allows us to show a “grayed out” group checkbox. TripleState gets turned back off in the group checkbox’s click event, so when you are clicking it the only possibilities are checked or not checked.

This class is pretty flexible. You can add rows, or checkboxes within rows, and it works correctly. Just be sure to add the controls within the appropriate group and follow the naming pattern of the existing controls.

The userform code looks like this:

Private cHeadFooterCheckboxes As New clsHeadFooterCheckboxes
Private collCheckBoxClasses As Collection
Private WithEvents ThisBook As Excel.Workbook

Private Sub UserForm_Initialize()

Set ThisBook = ThisWorkbook
InitializeClasses
SetWorksheetCombo
SetDisplayTextBoxes
End Sub

Sub InitializeClasses()
Dim ctl As MSForms.Control
Dim RowName As String

Set collCheckBoxClasses = New Collection
'For each group control
For Each ctl In Me.grpgroupControls.Controls
    RowName = Replace(ctl.Name, "chkAll", "")
    InitializeRowClasses RowName
Next ctl
End Sub

Sub InitializeRowClasses(RowType As String)
Dim collRowmembers As Collection
Dim ctl As MSForms.Control

Set collRowmembers = New Collection
For Each ctl In Me.grpmemberControls.Controls
    'If it's a checkbox in the row being processed
    If InStr(ctl.Name, RowType) > 0 Then
        collRowmembers.Add ctl, ctl.Name
    End If
Next
'Create a class for each member control in the row
For Each ctl In collRowmembers
    Set cHeadFooterCheckboxes = New clsHeadFooterCheckboxes
    'initialize the class with the
    'control, other members and group
    With cHeadFooterCheckboxes
        Set .memberCheckbox = ctl
        Set .groupCheckbox = Me.Controls("chkAll" & RowType)
        Set .collmemberCheckboxes = collRowmembers
        Set .ParentForm = Me
    End With
    'add the class to the collection
    collCheckBoxClasses.Add cHeadFooterCheckboxes
Next ctl
End Sub

End Sub

Private Sub cboWorksheets_Change()
If Me.cboWorksheets.Enabled Then
    SetDisplayTextBoxes
End If
End Sub

Private Sub ThisBook_SheetActivate(ByVal Sh As Object)
SetWorksheetCombo
End Sub

There’s other code in the userform that handles the worksheet combobox. You can download a sample workbook to see it all in action.

7 thoughts on “UserForm Event Handler Class – Multiple Controls

  1. That’s a great example Doug…and one I’m sure I’ll want to revisit, but will struggle to remember just where the heck I saw it!

    Good stuff.

  2. Nice work,

    I always dislike this bit:

    Set .ParentForm = Me

    But I have never been able to pass a form instance into a VBA Class as a parameter, like i feel i should do. – Glad to know I’m not the only one using this technique!!

    Ross

  3. Pingback: UserForm Event Class – Number Selector - yoursumbuddy

  4. Hi, I need help with event handlers and I don’t know what I am doing wrong. I have a form with a custom button and need a subroutine to be activated when clicked. Would appreciate all the help i can get. Thanks in advance.

    The code is below:

    ‘ userform code:
    Private nb As New nxtbtnhandler

    Private Sub UserForm_Initialize()
    add_nxt_btn
    End Sub

    Sub add_nxt_btn()
    Set nxtbtn = UserForm1.Controls.Add(“Forms.CommandButton.1”, i)
    With nxtbtn
    ‘.Name = “nbtn”
    ‘.Picture = LoadPicture(ActiveWorkbook.Path & “\” & “Rt arrow.jpg”)
    .Left = UserForm1.Width – 30
    .Width = 21
    .Top = 6
    .Height = 21
    Set nb = New nxtbtnhandler
    End With
    End Sub

    ‘ module code
    Option Explicit

    Sub showform()
    UserForm1.Show vbModeless
    End Sub

    ‘ nxtbtnhandler class module code
    Public WithEvents nxtbtn As MSForms.CommandButton

    Private Sub nxtbtn_Click()
    MsgBox “Hi”
    End Sub

    • Hi Shawn,

      Try this in your form module. I got rid of the subroutine, but you could add it back as a function that returns a button if need be:

      Private nb As nxtbtnhandler

      Private Sub UserForm_Initialize()
      Dim nxtbtn As MSForms.CommandButton

      Set nxtbtn = UserForm1.Controls.Add("Forms.CommandButton.1")
      With nxtbtn
          .Name = "nbtn"
          '.Picture = LoadPicture(ActiveWorkbook.Path & "" & "Rt arrow.jpg")
          .Left = UserForm1.Width - 30
          .Width = 21
          .Top = 6
          .Height = 21
      End With
      Set nb = New nxtbtnhandler
      Set nb.nxtbtn = nxtbtn
      End Sub

      The main problem is that you were never setting class’s nxtbtn to the new button you just created (the last line in my modified code).

      Note that in VBA, but not VB.Net, it’s good practice to Set your class with New in the body of the code, rather than using New in the class declaration.

      This concludes your one complimentary answer from YSB 🙂 . I hope it’s been helpful. If you have follow-up questions please get friendly with the zillions of smart folks at StackOverlflow or the forum of your choice.

      • Oh my God, Doug, you are a genius. It works. I was trying this for a couple of days with no success.

        Thank you so much. God Bless.

        Shawn

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>