Dynamically Added SpinButtons

[Multiverse]

Hi

I've been working on a UserForm which loads in data from a Worksheet range, and dynamically creates a varying number of TextBoxes. I now want to dynamically add a SpinButton to each line I'm displaying in the form. The SpinButton will increment or or reduce the value of a line by a penny (or a cent). How should I handle the SpinButton _Change() Sub when the number of buttons will be dynamic?

This is how I've created the buttons:

    For i = 3 To PostingsLastRow
        n = n + 1
        Set cSpinButton = FormInvoice.FrameInvLines.Controls.Add("Forms.SpinButton.1")
        With cSpinButton
            .Name = "SpinButton" & n
            .Orientation = fmOrientationHorizontal
            .Height = 15
            .Width = 50
            .Left = 940
            .Top = p
        End With
        p = p + 20
    Next i

I've uploaded an image so you can see when I'm going with this.

Many thanks!

 

[Domenic]

First, insert a class module...

Visual Basic Editor >> Insert >> Class Module

Then, in the Properties window under Name, give it the name clsSpinButton, and then enter the following code in the class module (customize the change event accordingly)...

Option Explicit

Public WithEvents MySpinButton As MSForms.SpinButton

Private Sub MySpinButton_Change()
    MsgBox MySpinButton.Name & " = " & MySpinButton.Value
End Sub

Then, in the userform code module, enter the following code...

Option Explicit

Dim spinButtonCollection As Collection

Private Sub UserForm_Initialize()
    Set spinButtonCollection = New Collection
   
    Dim cSpinButton As clsSpinButton
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "SpinButton" Then
            Set cSpinButton = New clsSpinButton
            Set cSpinButton.MySpinButton = ctrl
            spinButtonCollection.Add cSpinButton
        End If
    Next ctrl
End Sub

Hope this helps!

 

[Multiverse]

Hi!

This deffo helps! Thanks ever so much. I hadn't considered using a class was the way to go for this task. Can I ask... why does this code only work on initialize?

What I'm trying to do is have a frame that display invoice information. One SpinButton will iterate through invoices and bring each new invoice into the frame replacing the last one. Each invoice line has a SpinButton for each line to adjust rounding differences. I can't get the Spinbuttons created after initialize to work.

Thanks again for your help. Greatly appreciated
.

 

[Domenic]

Actually, each time a spinbutton is dynamically created, you can create a new instance of clsSpinButton, and add it to spinButtonCollection. So, you'll still have the class module, which defines our new class object. And, you'll still declare spinButtonCollection at the top of your userform code module. But then, instead of using the initialize event, you would amend your original code as follows. Note that for clarity I've changed the name of the variable that gets assigned a newly created spinbutton from cSpinButton to newSpinButton. Then I've declared cSpinButton as clsSpinButton to assign a new instance of our class object.

    Set spinButtonCollection = New Collection

    Dim cSpinButton As clsSpinButton
    Dim newSpinButton As MSForms.SpinButton
    Dim i As Long
    Dim n As Long
    Dim p As Long
    
    p = 25 'change the starting top position as desired
    For i = 3 To PostingsLastRow
        n = n + 1
        Set newSpinButton = FormInvoice.FrameInvLines.Controls.Add("Forms.SpinButton.1")
        With newSpinButton
            .Name = "SpinButton" & n
            .Orientation = fmOrientationHorizontal
            .Height = 15
            .Width = 50
            .Left = 940
            .Top = p
        End With
        Set cSpinButton = New clsSpinButton
        Set cSpinButton.MySpinButton = newSpinButton
        spinButtonCollection.Add cSpinButton
        p = p + 20
    Next i

Does this help?

 

[jkpieterse]

Don't forget to set the spinButtonCollection to nothing when the form terminates...

 

[Kyle123]

Don't forget to set the spinButtonCollection to nothing when the form terminates...

Why? I may have missed something, but I don't see a circular reference, as far as I can see the GC would have no issue with the code above - what have I missed?

 

[jkpieterse]

I agree there is no circular reference between the classes, but it is a good idea to set collections to nothing anyway.

 

[Kyle123]

Are you one of the camp that set everything to Nothing then? Or is there something about collections?

I’m afraid I’m in the opposite camp

 

[jkpieterse]

No i'm not strict about it, unless it's about collections of class instances, then I tend to set them to nothing once I'm done with them.

 

[Multiverse]

@Domenec Thanks ever so much. That code works perfectly.

Now that the spinButtons are working, the dynamic textboxes won't update as I want them to. When I debug.print their values, they have changed, but not on the form. Updating static textboxes works fine. Does this mean I have to create a class for my dynamic textboxes as well?

Many thanks