title: Dynamically add checkboxes and event handler to a worksheet
body: <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>