Unable to get ActiveX checkbox properties using application caller

Question

I'm trying to hide columns if their headings match a checkbox name. These ActiveX checkboxes have been created based on the column headings provided.

For iCol = colNum To totalColumns
        Set colCheckbox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1")
        With colCheckbox
            .Left = Cells(iCol + 2, 1).Left
            .Top = Cells(iCol + 2, 1).Top
            .Width = Cells(iCol + 2, 1).Width * 0.8
            .Height = Cells(iCol + 2, 1).Height * 0.8
            .Name = "cbx" & iCol
            .Object.Caption = Cells(RowNum, iCol).Value
        End With
    Next iCol

When the checkboxes are selected / deselected the code below will find the column heading and hide the column.

Private Sub CheckBox1_Click()

Set matchingAddress = Rows("4").Find(CheckBox1.Caption, LookIn:=xlFormulas)

If matchingAddress Is Nothing Then
 MsgBox ("Column Not Found")
Else
 cellAddressSplit = Split(matchingAddress.Address, "$")
 Columns(cellAddressSplit(1)).EntireColumn.Hidden = Not CheckBox1
End If

End Sub

I'm trying to move this code to a module, so that I can just pass the checkbox name (CheckBox1.Name) to the module function and manipulate the checkbox's visibility. I'm looking at 40 plus columns on the sheet and I want the same code in all Click methods. I'm trying to replace CheckBox1 with

Set cBox = ActiveSheet.CheckBoxes(Application.Caller)

or

Set cBox = ActiveSheet.Shapes(Application.Caller)

These are not working. Please provide directions. Should I be using forms instead of ActiveX checkboxes?

A sample error I get is "Unable to get checkboxes property of the worksheet class"

Even in a custom class I would still need to call the seeded object's methods. Would that not fail similarly? — Rahul
No, with the help of a collection and one Event Click() you can determine which Checkbox is clicked and act accordingly — EvR

Lee Li Fong Lee Li Fong · Accepted Answer · 2019-09-04T00:20:27

Sub CreateCheckBox()
colNum = 1
totalColumns = 3
RowNum = 8

For iCol = colNum To totalColumns
Set colCheckBox = ActiveSheet.CheckBoxes.Add(305.25, 158.25, 62.25, 17.25)
With colCheckBox
    .Left = Cells(iCol + 2, 1).Left
    .Top = Cells(iCol + 2, 1).Top
    .Width = Cells(iCol + 2, 1).Width * 0.8
    .Height = Cells(iCol + 2, 1).Height * 0.8
    .Name = "cbx" & iCol
    .Characters.Text = Cells(RowNum, iCol).Value
    .OnAction = "HideColumn"
End With
Next

End Sub

Sub HideColumn()

Set cBox = ActiveSheet.CheckBoxes(Application.Caller)
Set matchingAddress = Rows(8).Find(cBox.Caption, LookIn:=xlFormulas)

If matchingAddress Is Nothing Then
    MsgBox ("Column Not Found")
Else
    If Columns(matchingAddress.Column).EntireColumn.Hidden = True Then
        Columns(matchingAddress.Column).EntireColumn.Hidden = False
    Else
        Columns(matchingAddress.Column).EntireColumn.Hidden = True
    End If
End If

End Sub

Unable to get ActiveX checkbox properties using application caller

1 Answers