Class module to handle checkbox event

[tiredofit]

The following code works for ActiveX checkboxes:

ThisWorkbook:

Option Explicit

    Dim CheckBoxesColl As Collection
    
Private Sub Workbook_Open()

    Dim CheckBoxHandler As ClsCheckBoxEvent

    Set CheckBoxesColl = New Collection

    Dim MyShp As Shape

    For Each MyShp In Sheet1.Shapes
    
        With MyShp

            If .Type = msoOLEControlObject Then

                With .OLEFormat.Object

                    If TypeOf .Object Is MSForms.CheckBox Then

                        Set CheckBoxHandler = New ClsCheckBoxEvent

                        Set CheckBoxHandler.CheckGroup = .Object

                        CheckBoxesColl.Add Item:=CheckBoxHandler

                    End If

                End With

            End If

        End With
            
    Next MyShp
    
End Sub

ClsCheckBoxEvent:

Option Explicit

    Public WithEvents CheckGroup As MSForms.CheckBox

Private Sub CheckGroup_Click()

    Select Case CheckGroup.Caption
    
        Case "CheckBox1"
            
            Select Case CheckGroup.Value
            
                Case True
                
                    MsgBox "CheckBox 1 checked"
            
                Case False
            
                    MsgBox "CheckBox 1 unchecked"
                    
            End Select
            
        Case "CheckBox2"
        
            Select Case CheckGroup.Value
            
                Case True
                
                    MsgBox "CheckBox 2 checked"
            
                Case False
            
                    MsgBox "CheckBox 2 unchecked"
                    
            End Select
            
    End Select

End Sub

How can I adapt it so it works for Forms checkboxes? (I don't mean a checkbox on a userform but a checkbox under Developer -> Insert - Forms Controls).

Thanks

 

[dk]

Not sure if that can be done but if your aim is to have one procedure that handles multiple checkboxes then maybe you can use Application.Caller to determine which checkbox was clicked. Assign each checkbox to the FormsCheckBoxHandler macro.

Sub FormsCheckboxHandler()

Dim checkBoxName As String

checkBoxName = Application.Caller

MsgBox checkBoxName & " was clicked."

End Sub

Does that help?

 

[tiredofit]

Not sure if that can be done but if your aim is to have one procedure that handles multiple checkboxes then maybe you can use Application.Caller to determine which checkbox was clicked. Assign each checkbox to the FormsCheckBoxHandler macro.

Sub FormsCheckboxHandler()

Dim checkBoxName As String

checkBoxName = Application.Caller

MsgBox checkBoxName & " was clicked."

End Sub

Does that help?

Thanks, that'll do nicely.