Use the SpinButton_change() event where there is a

Question

I have a userform with various checkboxes. Each checkbox when selected opens up a new userform with a new set of checkbox, textbox and spinbutton controls based on a column in a worksheet. This column will change and thus there will be a new number of checkboxes, textbox and spinbutton controls every time the macro runs.

I need to use the spinbutton_change() event to set the value of the textbox. Since I don't know the number of spinbuttons that will exist at a given time how can I code this so each spinbutton updates its respective textbox?

Private Sub UserForm_Activate()
Dim NewChkBx As MSForms.CheckBox
Dim NewTxtBx As MSForms.TextBox
Dim NewSpinButton As MSForms.SpinButton
Dim rngSource As Range
Dim rngCell As Range
Dim TopPos As Integer
Dim MaxWidth As Long
Sheets("Background").UsedRange.ClearContents
'Filters items in Col A which Equal "Light"
Application.ScreenUpdating = False
Worksheets("Weight_Data").Unprotect
Worksheets("Background").[a1].CurrentRegion.Offset(1).ClearContents
Worksheets("Weight_Data").Range("D1:D1000").AutoFilter 1, "Light"
Worksheets("Weight_Data").Range("A2:H1000").Copy (Worksheets("Background").Range("A65536").End(xlUp)(2))
Worksheets("Weight_Data").Range("A1").AutoFilter 'Turn Filter Off
' End of filter
' Start of creating new userform based only on checked boxes.
With Worksheets("Background")
    Set rngSource = Worksheets("Background").Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
End With
TopPos = 5
MaxWidth = 0
For Each rngCell In rngSource
    If rngCell.Value <> "" Then
        Set NewChkBx = Me.Controls.Add("Forms.CheckBox.1")
        Set NewTxtBox = Me.Controls.Add("Forms.Textbox.1")
        Set NewSpinButton = Me.Controls.Add("Forms.Spinbutton.1")
        With NewChkBx
            .Caption = rngCell.Value
            .Left = 5
            .Top = TopPos
            .AutoSize = True
            If .width > MaxWidth Then MaxWidth = .width
        End With
        With NewTxtBox
            .Enabled = True
            .Value = 0
            .Enabled = False
            .Text = 0
            .Height = 15
            .width = 20
            .Left = NewChkBx.width + 20
            .Top = TopPos
            .AutoSize = True
            If .width > MaxWidth Then MaxWidth = .width
        End With
        With NewSpinButton
            .Value = 0
            .Height = 15
            .width = 10
            .Left = NewTxtBox.width + NewChkBx.width + 20
            .Top = TopPos
            If .width > MaxWidth Then MaxWidth = .width
        End With
        TopPos = TopPos + 60
        End If
     Next rngCell
     Me.width = MaxWidth + 60
     Me.Height = TopPos + 25
   ' Worksheets("Weight_Data").Protect
    Application.ScreenUpdating = True
    Sheets("Background").UsedRange.ClearContents
End Sub

Answer 1

Here's a slightly-simplified version of your code which has some dynamic event capture built in.

Userform:

Option Explicit

Dim col As Collection '<<< needs to be global

Private Sub UserForm_Activate()

    Dim x As Long, TopPos As Long, MaxWidth As Long
    Dim NewChkBx As MSForms.CheckBox
    Dim NewTxtBox As MSForms.TextBox
    Dim NewSpinButton As MSForms.SpinButton

    Set col = New Collection
    TopPos = 5
    MaxWidth = 0

    For x = 1 To 10

        Set NewChkBx = Me.Controls.Add("Forms.CheckBox.1")
        Set NewTxtBox = Me.Controls.Add("Forms.Textbox.1")
        Set NewSpinButton = Me.Controls.Add("Forms.Spinbutton.1")

        With NewChkBx
            .Caption = "Number " & x
            .Left = 5
            .top = TopPos
            .AutoSize = True
            If .Width > MaxWidth Then MaxWidth = .Width
        End With
        With NewTxtBox
            .Enabled = True
            .Value = 0
            .Enabled = False
            .Text = 0
            .Height = 15
            .Width = 20
            .Left = NewChkBx.Width + 20
            .top = TopPos
            .AutoSize = True
            If .Width > MaxWidth Then MaxWidth = .Width
        End With
        With NewSpinButton
            .Value = 0
            .Height = 15
            .Width = 10
            .Left = NewTxtBox.Width + NewChkBx.Width + 20
            .top = TopPos
            If .Width > MaxWidth Then MaxWidth = .Width
        End With

        col.Add CaptureEvents(NewSpinButton, NewTxtBox) '<<< save new class instance

        TopPos = TopPos + 20

     Next x

     Me.Width = MaxWidth + 60
     Me.Height = TopPos + 25

End Sub

'Return a new instance of clsEvents with controls added....
Private Function CaptureEvents(spn As MSForms.SpinButton, txt As MSForms.TextBox)
    Dim rv As New clsEvents
    Set rv.spn = spn
    Set rv.tb = txt
    Set CaptureEvents = rv
End Function

Class module "clsEvents":

Option Explicit

Public WithEvents spn As MSForms.SpinButton
Public tb As MSForms.TextBox

Private Sub spn_Change()
    Me.tb.Text = spn.Value
End Sub