2

I have a bunch of spinbuttons that control the values in textboxes. I'm using a Class to allow all of them to use the same change event. It works great with unformatted boxes.

However when I try to make the textboxes show + and - numbers it doesn't work correctly. Only the first spin button will work. It's value needs to be set to a negative number before the next spinbutton will work. No buttons (other than the first) will work correctly unless the textbox directly above it in order is set to a negative number.

I've tried using .Text instead of .Value but it doesn't make a difference.

Class Module:

Public WithEvents SpinBtn As MSForms.SpinButton
Public WithEvents StatBox As MSForms.TextBox

Private Sub StatBox_Change()
Call UserForm1.ChangeSpin
End Sub

Private Sub SpinBtn_Change()
Call UserForm1.ChangeStat
End Sub

UserForm Module:

Dim collSpin As New Collection
Dim collStat As New Collection

Public Sub ChangeStat()
    Dim i As Long

    For i = 1 To 4
         Me.Controls("StatBox" & i).Value = Me.Controls("SpinButton" & i).Value
    Next
End Sub

Public Sub ChangeSpin()
    Dim i As Long  

    For i = 1 To 4
        Me.Controls("SpinButton" & i).Value = Me.Controls("StatBox" & i).Value
        Me.Controls("StatBox" & i) = Format(Me.Controls("StatBox" & i), "+#;-#;+0")
        'This is the line that breaks things
    Next
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim ctl As MSForms.Control
    Dim obEvents As clsSpin

    Call ChangeSpin

    'Collect SpinButtons
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.SpinButton Then
            For i = 1 To 4
                If ctl.Name = "SpinButton" & i Then
                    Set obEvents = New clsSpin
                    Set obEvents.SpinBtn = ctl
                    collSpin.Add obEvents
                End If
            Next
        End If
    Next ctl

    'Collect StatBoxes
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Then
            For i = 1 To 4
                If ctl.Name = "StatBox" & i Then
                    Set obEvents = New clsSpin
                    Set obEvents.StatBox = ctl
                    collStat.Add obEvents
                End If
            Next
        End If
    Next ctl
End Sub

Edit: Thanks to @YowE3K for showing me a much easier and cleaner way!

Class:

Public WithEvents SpinBtn As MSForms.SpinButton
Public WithEvents StatBox As MSForms.TextBox

Private Sub StatBox_Change()
    'prevents error when enter + or -
    If IsNumeric(Me.StatBox.Value) = False Then
    Else
        'defaults to max or min of spinbutton when out of range
        Select Case Me.StatBox.Value
            Case Is < SpinBtn.Min
                Me.SpinBtn.Value = Me.SpinBtn.Min
            Case Is > SpinBtn.Max
                Me.SpinBtn.Value = Me.SpinBtn.Max
            Case Else
                Me.SpinBtn.Value = Me.StatBox.Value
        End Select
    Me.StatBox.Value = Format(Me.StatBox.Value, "+#;-#;+0")
    End If
End Sub

Private Sub SpinBtn_Change()
    Me.StatBox.Value = Format(Me.SpinBtn.Value, "+#;-#;+0")
End Sub

Private Sub StatBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case 43 '+
        Case 45 '-
        Case 48 To 57 '0-9
        Case Else
            KeyAscii = 0
    End Select
End Sub

Userform:

Dim collSpinStat As New Collection

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim obEvents As clsSpin

    For i = 1 To 4
        Set obEvents = New clsSpin
        Set obEvents.SpinBtn = Me.Controls("SpinButton" & i)
        Set obEvents.StatBox = Me.Controls("StatBox" & i)
        Me.Controls("StatBox" & i) = Format(Me.Controls("StatBox" & i), "+#;-#;+0")
        collSpinStat.Add obEvents
    Next
End Sub

1 Answer 1

3

It seems like you are trying to link the "StatBox" controls with the "SpinButton" controls. If so, try the following:

Class "clsSpin":

Public WithEvents SpinBtn As MSForms.SpinButton
Public WithEvents StatBox As MSForms.TextBox

Private Sub StatBox_Change()
    Me.SpinBtn.Value = Me.StatBox.Value
End Sub

Private Sub SpinBtn_Change()
    Me.StatBox.Value = Format(Me.SpinBtn.Value, "+#;-#;+0")
End Sub

UserForm module:

Dim collSpinStat As New Collection

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim obEvents As clsSpin

    For i = 1 To 4
        Set obEvents = New clsSpin
        Set obEvents.SpinBtn = Me.Controls("SpinButton" & i)
        Set obEvents.StatBox = Me.Controls("StatBox" & i)
        collSpinStat.Add obEvents
    Next
End Sub

FWIW, I would suggest using Labels instead of TextBoxes. Using TextBoxes means you will need to incorporate code into the StatBox_Change event to test whether the value entered by the user in the TextBox is actually valid or not. Using Labels means the user has to use the SpinButton to make the change.

Sign up to request clarification or add additional context in comments.

4 Comments

Perfect! Never knew you could change a value using Format. That's sweet. I did want users to be able to enter values in the text boxes. I'll add that in an edit for anyone else that happens upon this.
@bigbucky - Your Me.Controls("StatBox" & i) = Format(Me.Controls("StatBox" & i), "+#;-#;+0") is doing the same thing as my Me.StatBox.Value = Format(Me.SpinBtn.Value, "+#;-#;+0") - I just explicitly coded the Value property rather than letting it default to that property.
@bigbucky Oh! I just re-looked at my code and realised why the Collection will be required - without it the objects referred to by obEvents won't be maintained. With the collection, they will persist until the form is closed. I have edited the answer to not say that that Collection isn't really needed.
yep tried it without the Collection and realized I still needed it. I posted my full code in an edit. Works great and much cleaner. Thanks for your help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.