wire up events for dynamically created controls in frames in vba userform

Question

I am new here and I am struggling with VBA, especially the userform.

I want to create a userform that allows to check if everyone is present or not in a list of people.

The list of people can vary every day, so I made the choice to create a dynamic userform. I ask Excel to go to get the number of people in my list then I create a userform with all the names of these people and 2 optionbuttons at each line "here" or "absent".

My problem is this: my optionbuttons have been created in a FOR loop so they have the following names: "here" & i or "absent" & i (with i from 1 to the number of people on my list)

Now I wish I could create a code when you click on these buttons to update an excel sheet. Something like "Private sub here&i_click() ...."

But I don't know ow many buttons I have and the value of i is not changing anymore since we are out of the FOR loop.

Can someone help me with that? Thank you

Here is my code. When the userform rosterfill is shown I would like the different frames acting like that:

for i= 6 to lastrowd
If here & i is clicked then frame3 & i is visible
If absent & i is clicked then frame2 & i is visible
if none is clicked then msgbox ("select here/absent for everyone")
Next

For sure I understand it cannot be done with this kind of loop but I can't find any example online with my situation...

-- VBA CODE---

Workbooks.Open (pathroot & "\" & dpt & "\Roster")
Worksheets("Shift " & shi).Activate
lastrowd = Cells(Rows.Count, 1).End(xlUp).Row
topini = 18
initop = 18

For i = 6 To lastrowd
Set thelabel = rosterfill.Controls.Add("Forms.TextBox.1", "label" & i, True)
With thelabel
    .Value = Cells(i, 1).Value 'name of the person
    .Left = 6
    .Width = 234
    .Top = topini + 30
    .Locked = True
    .Height = 24
End With

Set theframe = rosterfill.Controls.Add("Forms.Frame.1", "frame" & i, True)
With theframe
    .Top = initop + 30
    .Width = 100
    .Left = 246
    .Height = 24

    Set here = .Controls.Add("forms.OptionButton.1", "here" & i, True)
        With here
        .Height = 18
        .Left = 5
        .Width = 108
        .Caption = "here"
        End With


    Set absent = .Controls.Add("forms.OptionButton.1", "absent" & i, True)
        With absent
        .Height = 18
        .Left = 50
        .Width = 108
        .Caption = "absent"
        End With

End With

Set theframe2 = rosterfill.Controls.Add("Forms.Frame.1", "frame2" & i, False)
With theframe2
    .Top = initop + 30
    .Width = 350
    .Left = 350
    .Height = 24

    Set abs1 = .Controls.Add("forms.OptionButton.1", "abs1" & i, True)
        With abs1
        .Height = 18
        .Left = 5
        .Width = 108
        .Caption = "PTO"
        End With

    Set abs2 = .Controls.Add("forms.OptionButton.1", "abs2" & i, True)
        With abs2
        .Height = 18
        .Left = 50
        .Width = 108
        .Caption = "UPTO"
        End With

    Set abs3 = .Controls.Add("forms.OptionButton.1", "abs3" & i, True)
        With abs3
        .Height = 18
        .Left = 95
        .Width = 108
        .Caption = "MED"
        End With

    Set abs4 = .Controls.Add("forms.OptionButton.1", "abs4" & i, True)
        With abs4
        .Height = 18
        .Left = 140
        .Width = 108
        .Caption = "ANCI"
        End With

    Set abs5 = .Controls.Add("forms.OptionButton.1", "abs5" & i, True)
        With abs5
        .Height = 18
        .Left = 185
        .Width = 108
        .Caption = "FMLA"
        End With

    Set abs6 = .Controls.Add("forms.OptionButton.1", "abs6" & i, True)
        With abs6
        .Height = 18
        .Left = 230
        .Width = 108
        .Caption = "AE"
        End With

    Set abs7 = .Controls.Add("forms.OptionButton.1", "abs7" & i, True)
        With abs7
        .Height = 18
        .Left = 260
        .Width = 108
        .Caption = "S"
        End With

    Set abs8 = .Controls.Add("forms.OptionButton.1", "abs8" & i, True)
        With abs8
        .Height = 18
        .Left = 285
        .Width = 108
        .Caption = "BRV"
        End With

    Set abs9 = .Controls.Add("forms.OptionButton.1", "abs9" & i, True)
        With abs9
        .Height = 18
        .Left = 320
        .Width = 108
        .Caption = "JD"
        End With

End With

Set hours = rosterfill.Controls.Add("forms.TextBox.1", "hours" & i, False)
    With hours
        .Left = 700
        .Width = 50
        .Top = topini + 30
        .Height = 24
    End With
Set Comment = rosterfill.Controls.Add("forms.TextBox.1", "comment" & i, False)
    With Comment
        .Left = 755
        .Width = 100
        .Top = topini + 30
        .Height = 24
    End With


Set theframe3 = rosterfill.Controls.Add("Forms.Frame.1", "frame3" & i, False)
With theframe3
    .Top = initop + 30
    .Width = 350
    .Left = 350
    .Height = 24

    Set here1 = .Controls.Add("forms.OptionButton.1", "here1" & i, True)
        With here1
        .Height = 18
        .Left = 5
        .Width = 108
        .Caption = "LE"
        End With

    Set here2 = .Controls.Add("forms.OptionButton.1", "here2" & i, True)
        With here2
        .Height = 18
        .Left = 50
        .Width = 108
        .Caption = "AL"
        End With

    Set here3 = .Controls.Add("forms.OptionButton.1", "here3" & i, True)
        With here3
        .Height = 18
        .Left = 95
        .Width = 108
        .Caption = "Entire Shift"
        End With
End With

topini = topini + 30
initop = initop + 30
Next i

If topini + 100 > 450 Then
rosterfill.Height = 450
rosterfill.CommandButton1.Top = topini + 100 - 60
rosterfill.CommandButton2.Top = topini + 100 - 60
rosterfill.ScrollBars = fmScrollBarsVertical
rosterfill.ScrollHeight = topini + 100
rosterfill.ScrollWidth = 50
rosterfill.ScrollTop = 0
Else
rosterfill.Height = topini + 100
rosterfill.CommandButton1.Top = topini + 100 - 60
rosterfill.CommandButton2.Top = topini + 100 - 60
End If
rosterfill.Show

You have to use Classes. You'll find plenty of example in the NET and in SO mainly — HTH, Apr 09 '20 at 14:38
Not trivial, but very doable. It sounds like you want to wire up events for dynamically created controls. I recently answered 2 similar questions. Here's [a link](https://stackoverflow.com/a/60996223/5162073) to the most current. — Brian M Stafford, Apr 09 '20 at 14:40
Have you considered using a listbox with check boxes see https://learn.microsoft.com/en-us/office/vba/Language/Concepts/Forms/create-a-list-box-with-option-buttons-or-check-boxes. With only 2 options a check box per person is maybe all you need . Use ListBox1_Change event to update sheet. — CDP1802, Apr 09 '20 at 15:40
You could also create a "Submit" button with code that loops through all checkboxes and validates whether all people were checked here or absent and then updates the sheet according to that. — Nacorid, Apr 09 '20 at 16:39
I edited my question with my code so maybe this is more clear... I am trying to do more complicated than just here or absent. The person filling the roster will have to select other options after depending if he clicked here or absent. I hope someone can help me because I am fairly new to VBA and this is very complicated for me — Anne, Apr 09 '20 at 17:07
@Anne The answer you seek is in [this link](https://stackoverflow.com/a/60766557/5162073). The main change is to modify the code to replace the CommandButton with an OptionButton. — Brian M Stafford, Apr 11 '20 at 15:41

wire up events for dynamically created controls in frames in vba userform

0 Answers0