Dynamically add checkboxes and event handler to a worksheet

Question

In my sheet in column B I have names of components In column A I would like to have checkboxes which are dynamically added, as the list of names in column B will increase over time

I need to be able to do the following: - handle the events (onclick) as ticking/unticking the checkboxes hides/unhides rows in another sheet - return the status of each checkbox as I cycle through the checkboxes in another module (onclick from a commandbox) and depending on the status an action follows or not - modify the status of each checkbox as I have 1 commandbox to tick them all and 1 commandbox to untick them all

So far I have a working prototype, but I have 2 problems: 1) the checkboxes are not linked to the cells in column A, they are just positioned when I created them 2) the checkboxes are not dynamic, I created them manually and had to write an event handler (onclick) for each checkbox (> 50 checkboxes)

I have tried to create a code to dynamically add checkboxes and create a class module to handle the events, but I am really stuck.. I copied and modified some code that was originally intended for a userform and I managed to make it work on a userform, but I'd rather have everything on the worksheet as i described above.

Here is the class module code (named: clsBoxEvent)

Option Explicit
Public WithEvents cBox As MSForms.CheckBox

Private Sub cBox_Click()
MsgBox cBox.Name
End Sub

Here is the code I wrote as a module. I plan to put it in an event (onclick) from a command button which I plan to click to update the list of checkboxes. Unless this is not necessary as there is a way that the checkboxes are created as soon as the cell in column B isn't blank ?

I thank you for your input.

Dim chkBoxEvent As clsBoxEvent
Dim chkBox As MSForms.CheckBox
Dim chkBoxColl As Collection

Private Sub chkBox_update()
Dim i As Integer
Set chkBoxColl = New Collection

For i = 1 To 5
    ' I wrote the code just to add 5 checkboxes as a test. Later I will need to adapt this to the actual required number of checkboxes (the number of products in column B)  

    Set chkBox = Controls.Add("Forms.CheckBox.1", "ChkBox" & i)
    With chkBox
       ' I work with the position as I did not know how to link it to the cells in colums A
        .Left = 126
        .Height = 16
        .Top = 6 + ((i - 1) * 16)
    End With

Set chkBoxEvent = New clsBoxEvent
Set chkBoxEvent.cBox = Controls(chkBox.Name)
chkBoxColl.Add chkBoxEvent

Next i

End Sub

Answer 1

My answer to: Excel VBA script to insert multiple checkboxes linked to cell with yes and no instead of true and false seems like it will work nicely for you.

Sub AddCheckBoxes()

    Dim cb As CheckBox
    Dim myRange As Range, cel As Range
    Dim wks As Worksheet

    Set wks = Sheets("Sheet1")

    Set myRange = wks.Range("A1:A1000")

    For Each cel In myRange

        Set cb = wks.CheckBoxes.Add(cel.Left, cel.Top, 30, 6)

        With cb
            .Caption = ""
            .OnAction = "ProcessCheckBox"
        End With

    Next

End Sub

Sub ProcessCheckBox()
    Dim cb As CheckBox
    With Sheets("Sheet1")
        Set cb = .CheckBoxes(Application.Caller)
        If Not cb Is Nothing Then cb.TopLeftCell = IIf(cb.Value = 1, "Cleared", "")
    End With
End Sub