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