Dynamically add checkboxes and event handler to a worksheet
<p>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</p>

<p>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</p>

<p>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)</p>

<p>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.</p>

<p>Here is the class module code (named: clsBoxEvent)</p>

<pre><code>Option Explicit
Public WithEvents cBox As MSForms.CheckBox

Private Sub cBox_Click()
MsgBox cBox.Name
End Sub
</code></pre>

<p>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 ?</p>

<p>I thank you for your input.</p>

<pre><code>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" &amp; 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
</code></pre>