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.
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:
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 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.
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.
Just send me an email and I’ll remind you :).
Thanks for the kind words!
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
Pingback: UserForm Event Class – Number Selector - yoursumbuddy
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 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
Magnificent work, thank you so much for your sharing, it helps me a lot.
Best regards
Hakan