VBA: how to use class module to change value of checkbox in userform

Question

I have a userform with a lot of emailaddresses. I want the user to be able to select who to send an email to. I do so with checkboxes which are created on run time. To make it easier to use, I have also added a checkbox which allows the user to select of deselect all checkboxes.

This works perfectly as I want it to, but there is one problem I'm breaking my head over. If all checkboxes are checked and one gets unchecked, I want the "Select all" checkbox to be unchecked as well - and vice versa, if not all checkboxes were checked and the final checkbox is being checked by the user I want the "Select all" checkbox to be checked as well.

I try to do this using a class module. My overall knowledge of vba is pretty okay, but class modules are new territory for me, so excuse me if my language gets a bit fussy now.

In the initialize event of the userform I create a new collection and I assign clicks to these specific checkboxes. That works perfectly, as it doesn't give any errors in the initialize event of the userform and an event is triggered once I click one of these checkboxes. The problem I'm having is that I can't get a grip on the "Select all" checkbox (chkSelAll) in the userform. I've tried creating a public object for this checkbox in the userform (Public objSelAll As MSForms.CheckBox), but still it gives me the "Variable not defined" error once I click one of the checkboxes.

Here's the code for the class module (cls_RIRI):

Private WithEvents chkBox As MSForms.CheckBox
Public Sub AssignClicks(ctrl As Control)

     Set chkBox = ctrl

End Sub
Private Sub chkBox_Click()

    If chkBox.Value = False Then objSelAll.Value = False
                                '^This is where the error occurs: variable not defined
End Sub

And here's the relevant part of the Userform_Initialize event:

Private colTickBoxes As Collection
Public objSelAll As MSForms.CheckBox

Private Sub UserForm_Initialize()

Dim ChkBoxes As cls_RIRI
Dim ctrl As Control

Set objSelAll = Me.Controls.Item("chkSelAll")

Set colTickBoxes = New Collection

    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" And Left(ctrl.Name, 1) = "M" Then
            Set ChkBoxes = New cls_RIRI
            ChkBoxes.AssignClicks ctrl
            colTickBoxes.Add ChkBoxes
        End If
    Next ctrl

Set ChkBoxes = Nothing
Set ctrl = Nothing

End Sub

As you can see I didn't get to the point yet where I let the code check if all checkboxes are checked so that the select all checkbox can be checked as well. I'm not really looking for this code, I'll probably manage it once I get grip on the select all checkbox from the class module, so please don't worry about this part! :)

Nathan_Sav Nathan_Sav · Accepted Answer · 2016-02-02T14:01:11

Private WithEvents chkBox As MSForms.CheckBox
private strParentFormName as string
Public Sub AssignClicks(ctrl As Control,strFormName as string)
    strParentFormName=strFormName
    .....
end sub

Private Sub chkBox_Click()

    dim f as userform
    set f=userforms(0) <--- or loop the userforms to get form name
    If chkBox.Value = False Then f.controls("objSelAll").Value = False
                                '^This is where the error occurs: variable not defined
End Sub

and something like this

Public Function GET_USERFORM(strUserform As String) As UserForm

    Dim i As Integer

    For i = 0 To UserForms.Count - 1
        If UserForms(i).Name = strUserform Then
            Set GET_USERFORM = UserForms(i)
            Exit For
        End If
    Next i

End Function

VBA: how to use class module to change value of checkbox in userform

2 Answers