VBA WithEvents not working

Question

I am basically creating a list of items that are generated at runtime. The items are listed on a userform as labels(the items are stored in a linked list). With each item, I want to add a spinbutton so I can move the items up and down the list. I the spinbuttons are created just fine, the events I have coded do not work?? I am not sure what I am doing wrong. Probably something simple...

This is the class module to hold the events: cls_Spin_Btn

Private WithEvents Spin_Events As SpinButton

Private Sub Spin_Events_SpinUp()

    Debug.Print "Hey. Spin button worked."

End Sub

Public Property Set SetNewSpinButtion(newSpinBtn As MSForms.SpinButton)

    Set Spin_Events = newSpinBtn

End Property

This is code is calling from a module:

Function AddRunToForm(f As UserForm, r As ProductionRun, top As Integer) As Integer

Dim Run_SpinBtn As MSForms.SpinButton
Dim spinBtn As cls_Spin_Btn

Set Run_SpinBtn = f.Controls.Add("Forms.SpinButton.1", r.ProdID & "_SBtn", True)
Set spinBtn = New cls_Spin_Btn

With Run_SpinBtn

    .top = ProdID_Lbl.top
    .Left = 5
    .height = 10
    .Width = 12
    .height = 18
    .Visible = True

End With

Set spinBtn.SetNewSpinButtion = Run_SpinBtn

AddRunToForm = ProdID_Lbl.top + ProdID_Lbl.height

End Function

This code is called from a loop in the same module creating labels and spinbuttons for each item. What am I doing wrong? Any help would be very much appreciated.

You need to keep each `spinBtn` in scope (eg) by adding it to a collection with global (module-level) scope. See Gary's answer here: http://stackoverflow.com/questions/1083603/vba-using-withevents-on-userforms — Tim Williams, Aug 07 '12 at 16:11

score 1 · Accepted Answer · answered Aug 07 '12 at 18:51

In your userform code module, put this

Private mcolSpinButtons As Collection

Public Property Get SpinButtonCollection() As Collection

    If mcolSpinButtons Is Nothing Then Set mcolSpinButtons = New Collection

    Set SpinButtonCollection = mcolSpinButtons

End Property

That will give you access to a module level variable that will stay in scope as long as your userform is open. When you put cls_Spin_Btn instances in that collection, they will also stay in scope.

Then in your function, once you create the new spin button class instance, add it to the collection

f.SpinButtonCollection.Add spinBtn, spinBtn.Name

Works like a charm! That was the simple thing I missed. Thank you very much. — Roi-Kyi Bryant, Aug 07 '12 at 23:04

VBA WithEvents not working

1 Answers1