Add event on runtime

Question

I've created a List of elements in VBA with a for each loop like this:

Dim WithEvents olblCount As MSForms.Label
Dim WithEvents otxtGet As MSForms.TextBox
Dim WithEvents ospinGet As MSForms.SpinButton

Private Sub UserForm_Layout()
    i = 0
    For Each C In Range("Anzahl")
        If C = "" Then
            Exit For
        End If
        Set olblCount = Controls.Add("Forms.Label.1", "lblCount" + CStr(i), True)
        With olblCount
            .Font.Size = 12
            .Left = 110
            .Top = 12 + i * 24
            .Height = 18
            .Width = 30
            .TextAlign = fmTextAlignRight
            .SpecialEffect = fmSpecialEffectBump
            .Caption = C
        End With

        Set otxtGet = Controls.Add("Forms.TextBox.1", "txtGet" + CStr(i), True)
        With otxtGet
            .Font.Size = 12
            .Left = 155
            .Top = 12 + i * 24
            .Height = 18
            .Width = 35
            .TextAlign = fmTextAlignRight
            .Text = 1
        End With

        Set ospinGet = Controls.Add("Forms.SpinButton.1", "spinGet" + CStr(i), True)
        With ospinGet
            .Left = 188
            .Top = 12 + i * 24 - 1
            .Height = 18
            .Width = 12
            .Value = 1
        End With
        i = i + 1
    Next
End Sub

Now I need a EventHandler to each spinButton with a conection on the txt Get in front of it.

When I manually create the spinButtons it would be like this:

Private Sub SpinGet_Change()
    txtGet1.Value = SpinButton1.Value
    SpinButton1.Max = txtCount.Caption
    SpinButton1.Min = 1
End Sub

But how can I create them in the script for each spinButton?

Check this out: http://stackoverflow.com/questions/10592641/assign-event-handlers-to-controls-on-user-form-created-dynamically-in-vba — DanL, Sep 18 '15 at 07:32

score 0 · Answer 1 · answered Sep 18 '15 at 08:26

0

Use the same logic, as you've used the WithEvents keyword you can access the event handlers for that object e.g.:

Private Sub ospinGet_Change()
   '// Your code here...
End Sub

Just make sure these event handlers are in the class module also.

answered Sep 18 '15 at 08:26

SierraOscar

17,507
6
40
68

Add event on runtime

1 Answers1