Hey Anne, great job getting this far with your dynamic attendance-tracking UserForm—dynamic controls can be tricky to work with, but using a class module is the standard way to bind events to them. Let’s walk through how to implement your requirements step by step.

Step 1: Create a Class Module for OptionButton Events #

First, we need a class to capture the click events of our dynamically created OptionButtons. Here’s how to set it up:

1. In the VBA Editor, right-click your project > Insert > Class Module.
2. Rename the class module (in the Properties window) to clsOptionButtonEvents.
3. Paste this code into the class module:

Option Explicit

' Declare an OptionButton that can trigger events
Public WithEvents optBtn As MSForms.OptionButton
Private parentForm As MSForms.UserForm

' Pass the parent UserForm to the class when initializing
Public Sub Initialize(ByVal form As MSForms.UserForm)
    Set parentForm = form
End Sub

Private Sub optBtn_Click()
    Dim ctrlName As String
    Dim i As Integer
    Dim ws As Worksheet
    
    ' Extract the index number (i) from the control's name
    ctrlName = optBtn.Name
    i = CLng(Right(ctrlName, Len(ctrlName) - InStr(ctrlName, "&")))
    
    ' Reference your target worksheet directly (avoid using Activate!)
    Set ws = Workbooks(pathroot & "\" & dpt & "\Roster").Worksheets("Shift " & shi)
    
    ' Handle clicks on "here" OptionButtons
    If Left(ctrlName, 4) = "here" Then
        ' Show frame3&i and hide frame2&i
        parentForm.Controls("frame3" & i).Visible = True
        parentForm.Controls("frame2" & i).Visible = False
        
        ' Update Excel with "Here" status
        ws.Cells(i, 2).Value = "Here" ' Adjust the column number to match your sheet
        
        ' Check if a detailed attendance type was selected
        Dim hasHereSubOpt As Boolean
        hasHereSubOpt = False
        For Each optBtn In parentForm.Controls("frame3" & i).Controls
            If optBtn.Value Then
                hasHereSubOpt = True
                ws.Cells(i, 3).Value = optBtn.Caption ' Store the detailed type
                Exit For
            End If
        Next
        
        If Not hasHereSubOpt Then
            MsgBox "Please select a detailed attendance type for " & ws.Cells(i, 1).Value, vbExclamation
        End If
    
    ' Handle clicks on "absent" OptionButtons
    ElseIf Left(ctrlName, 6) = "absent" Then
        ' Show frame2&i and hide frame3&i
        parentForm.Controls("frame2" & i).Visible = True
        parentForm.Controls("frame3" & i).Visible = False
        
        ' Update Excel with "Absent" status
        ws.Cells(i, 2).Value = "Absent" ' Adjust column number as needed
        
        ' Check if a detailed absence type was selected
        Dim hasAbsSubOpt As Boolean
        hasAbsSubOpt = False
        For Each optBtn In parentForm.Controls("frame2" & i).Controls
            If optBtn.Value Then
                hasAbsSubOpt = True
                ws.Cells(i, 3).Value = optBtn.Caption ' Store the detailed type
                Exit For
            End If
        Next
        
        If Not hasAbsSubOpt Then
            MsgBox "Please select a detailed absence type for " & ws.Cells(i, 1).Value, vbExclamation
        End If
    End If
End Sub

Step 2: Modify Your UserForm Code to Bind Events #

We need to track our class instances in a collection (otherwise they’ll be destroyed when the loop ends, and events won’t trigger). Update your UserForm module with these changes:

1. Add these declarations at the top of your UserForm module (outside any subroutine):

Option Explicit
Private optBtnEventsCollection As Collection

2. Update your control-creation loop to bind events to each "here" and "absent" OptionButton:

Private Sub UserForm_Initialize()
    ' Initialize the collection to hold our class instances
    Set optBtnEventsCollection = New Collection
    
    ' Open the roster workbook and reference the worksheet directly
    Dim ws As Worksheet
    Workbooks.Open (pathroot & "\" & dpt & "\Roster")
    Set ws = Worksheets("Shift " & shi)
    lastrowd = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    topini = 18
    initop = 18
    
    For i = 6 To lastrowd
        ' --- Your existing code to create the name label ---
        Set thelabel = rosterfill.Controls.Add("Forms.TextBox.1", "label" & i, True)
        With thelabel
             .Value = ws.Cells(i, 1).Value
             .Left = 6
             .Width = 234
             .Top = topini + 30
             .Locked = True
             .Height = 24
        End With
        
        ' --- Create the main frame with "here" and "absent" buttons ---
        Set theframe = rosterfill.Controls.Add("Forms.Frame.1", "frame" & i, True)
        With theframe
             .Top = initop + 30
             .Width = 100
             .Left = 246
             .Height = 24
             
             ' Create "here" button and bind its event
             Set here = .Controls.Add("forms.OptionButton.1", "here" & i, True)
             With here
                  .Height = 18
                  .Left = 5
                  .Width = 108
                  .Caption = "here"
             End With
             BindOptionButtonEvent here, rosterfill ' Add this line to bind the event
             
             ' Create "absent" button and bind its event
             Set absent = .Controls.Add("forms.OptionButton.1", "absent" & i, True)
             With absent
                  .Height = 18
                  .Left = 50
                  .Width = 108
                  .Caption = "absent"
             End With
             BindOptionButtonEvent absent, rosterfill ' Add this line to bind the event
        End With
        
        ' --- Rest of your existing code to create frame2, frame3, hours, comment, etc. ---
        ' (You can bind events to the sub-option buttons too, using the same BindOptionButtonEvent sub)
        
        topini = topini + 30
        initop = initop + 30
    Next i
    
    ' --- Your existing code to adjust UserForm height and scrollbars ---
    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
End Sub

' Helper subroutine to bind an OptionButton to our class event handler
Private Sub BindOptionButtonEvent(ByVal optBtn As MSForms.OptionButton, ByVal parentForm As MSForms.UserForm)
    Dim optEvent As clsOptionButtonEvents
    Set optEvent = New clsOptionButtonEvents
    Set optEvent.optBtn = optBtn
    optEvent.Initialize parentForm
    optBtnEventsCollection.Add optEvent
End Sub

Key Adjustments & Tips #

• Avoid Activate/Select: I replaced worksheet activation with a direct worksheet variable (ws)—this makes your code more reliable and faster.
• Excel Columns: Adjust the column numbers (like ws.Cells(i, 2).Value) to match where you want to store attendance data in your roster sheet.
• Sub-option Events: If you want to trigger updates when users select the detailed attendance/absence options (here1-here3, abs1-abs9), just add BindOptionButtonEvent [subOptName], rosterfill after creating each of those controls.
• Variable Scope: Make sure pathroot, dpt, and shi are declared in the UserForm module or are accessible to the code (they should be either public variables or passed into the UserForm when it’s loaded).

内容的提问来源于stack exchange，提问作者Anne