add multiple dynamic controls to userform and assi

Question

I am trying to add multiple spin button with each of them linked to different sets of cells that have some values assigned to them. i have tried adding the controls and use a class module to add the event handler procedure to them but to no avail. any help would be appreciated.

Dim spinArray() As New Class1
Private Sub UserForm_Initialize()
Dim i As Long
Dim quantspin As MSForms.SpinButton


subassy_break.Height = pnum1 * 70
subassy_break.Width = 500
With Label_Var    
    .Top = 15
    .Left = subassy_break.Width - (Label_Var.Width + 15)
    .Caption = msg
    .AutoSize = True
    .Font.Bold = True
End With

With UserForm
 For i = 1 To pnum1
    Set quantspin = Me.Controls.Add("Forms.spinbutton.1", "Quantity_Count_"  & i)
     With quantspin
       .Min = 0
       .SmallChange = 1
       .Max = 1
       .Left = 200
       .Top = subassy_break.height- pnum1*20
     End With
Next i
End With
End Sub

also the new class module that i have added is

Public WithEvents spinevents As MSForms.SpinButton

Private Sub spinevents_change()
    For i = 1 To pnum1
        Cells(userow + i, usecol).Value = spinevents.Value
    Next i
End Sub

Answer 1

This should help you figure it out:

clsSpin

Public WithEvents spinevents As MSForms.SpinButton
Public TargetCell As Range  '<<the cell to operate on

Private Sub spinevents_change()
    TargetCell.Value = spinevents.Value
End Sub

UserForm (simplified to show the relevant parts)

Dim spinners As Collection '<<< holds your clsSpin objects

Private Sub UserForm_Initialize()
    Dim i As Long, s As clsSpin, quantspin

    Set spinners = New Collection


    For i = 1 To 5
        Set quantspin = Me.Controls.Add("Forms.spinbutton.1", "Quantity_Count_" & i)
        With quantspin
            .Min = 0
            .SmallChange = 1
            .Max = 100
            .Left = 20 * i
            .Top = 50
        End With

        'create a new instance of the class, set some properties
        '  and add it to the collection
        Set s = New clsSpin
        Set s.spinevents = quantspin
        Set s.TargetCell = ThisWorkbook.Sheets(1).Cells(i, 1)
        spinners.Add s

    Next i

End Sub