0

I have a list of checkboxes on a userform created from a list on an Excel sheet. I want to have an additional checkbox that when checked, is testing the caption value of all the checkboxes in the list. If there is a match to a target string, these checkboxes should check as well. I can create the list but referencing the checkboxes after creation and triggering events is the issue. So far this is all I have:

Private Sub UserForm_Initialize()

Dim curColumn   As Long
Dim LastRow     As Long
Dim i           As Long
Dim chkBox      As MSForms.CheckBox

curColumn = 1 'column index

LastRow = Worksheets("Parts").Cells(Rows.Count, curColumn).End(xlUp).Row

For i = 2 To LastRow
    Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    chkBox.Caption = Worksheets("Parts").Cells(i, curColumn).Value
    chkBox.Left = 5
    chkBox.Top = 25 + ((i - 1) * 20)
    chkBox.Width = 200
Next i

End  Sub
Jeff Sauzeat
  • 1
  • 1
  • 2
  • You do not need to trigger an event to check a checkbox. As you create the checkboxes, add them to a (global to the form) Collection object. You can then loop over them at any point and check their Caption property. – Tim Williams Apr 25 '17 at 16:00
  • I'm not following. Whether the checkboxes are in a collection object or not, as the user interacts with the form and makes changes, I still need to trigger an event at the moment some specific checkboxes are checked, don't I? – Jeff Sauzeat Apr 25 '17 at 21:47
  • If you need to capture events from dynamically-created controls then there's an approach for that: see for example Siddharth Rout's answer here - http://stackoverflow.com/questions/10224511/assign-code-to-a-button-created-dynamically That is for buttons, but same approach applies for other types of controls. – Tim Williams Apr 25 '17 at 23:05
  • Also: http://www.siddharthrout.com/2011/08/05/vba-control-arrays/ – Tim Williams Apr 25 '17 at 23:08
  • Updated link of above http://www.siddharthrout.com/index.php/2018/01/15/vba-control-arrays/ – Siddharth Rout Dec 18 '18 at 13:53

0 Answers0