VBA Spin buttons in user form - how to declare them as a variable?

[Kra]

Hi all!

I have a user form with lots of text boxes and spin buttons that work as a time picker. So one textbox shows value of spin button (from -1 to 25) for hours and other one shows value between -1 and 60 for minutes.

Each of this spin buttons contains the same code:


For hours:

Private Sub SpinButton1_Change()

If Me.SpinButton1.Value = 24 Then Me.SpinButton1.Value = 0
If Me.SpinButton1.Value = -1 Then Me.SpinButton1.Value = 23

Me.TXT1.Value = Format(Me.SpinButton1.Value, "00")

End Sub

For minutes:

Private Sub SpinButton2_Change()

If Me.SpinButton2.Value = 60 Then Me.SpinButton2.Value = 0
If Me.SpinButton2.Value = -1 Then Me.SpinButton2.Value = 59

Me.TXT2.Value = Format(Me.SpinButton2.Value, "00")

End Sub

I have about 30 of these spin buttons (15 for hours and 15 for minutes), so I wonder if it is possible to create Public Sub or Function with this code and call it for each button change even, passing information like Button name and TextBox name?

 

[DanteAmor]

Hi @Kra. Thanks for posting on the forum.

Do the following:

1. Create a class module. In the VBA menu, Insert, Class Module. (Class1)
2. Put the following code.

Option Explicit

Public WithEvents MultSpin As MSForms.SpinButton    '<---At the start of all code

Private Sub MultSpin_Change()
  Dim sName As String
  Dim n As Long
  sName = MultSpin.Name
  n = Mid(sName, 11)
  If n Mod 2 = 1 Then
    'odd number
    If MultSpin.Value = 24 Then MultSpin.Value = 0
    If MultSpin.Value = -1 Then MultSpin.Value = 23
  Else
    'even number
    If MultSpin.Value = 60 Then MultSpin.Value = 0
    If MultSpin.Value = -1 Then MultSpin.Value = 59
  End If
  UserForm2.Controls("TXT" & n).Value = Format(MultSpin.Value, "00")
End Sub

3. Verify that the class module name is Class1:

4. Change UserForm2 to the name of your userform on this line of the class module.

UserForm2.Controls("TXT" & n).Value = Format(MultSpin.Value, "00")

5. According to your example. Spinbutton1 corresponds to TXT1, spinbutton2 to TXT2, and I guess spinbutton3 to TXT3 and so on.
I was also able to deduce that the 1 is for the hours and the 2 is for the minutes, so I also suppose that the 3 is for the hours and the 4 for the minutes, the 5 for hours and the 6 for the minutes, so the odd numbers are for the hours and the even for the minutes. If that pattern is correct, then the code in the class module is correct.

6. Now put the following code in the Initialize event of your userform:

Dim SpinBt() As New Class1      '<---At the start of all code

Private Sub UserForm_Initialize()
   Dim i As Long
   ReDim SpinBt(1 To 30)    'change to 30
   For i = 1 To UBound(SpinBt)
      Set SpinBt(i).MultSpin = Me.Controls("SpinButton" & i)
   Next i
End Sub

7. Run your userform and verify the functionality of the spinbuttons and textboxes.
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Kra]

Hi @Kra. Thanks for posting on the forum.

Do the following:

1. Create a class module. In the VBA menu, Insert, Class Module. (Class1)
2. Put the following code.

Option Explicit

Public WithEvents MultSpin As MSForms.SpinButton    '<---At the start of all code

Private Sub MultSpin_Change()
  Dim sName As String
  Dim n As Long
  sName = MultSpin.Name
  n = Mid(sName, 11)
  If n Mod 2 = 1 Then
    'odd number
    If MultSpin.Value = 24 Then MultSpin.Value = 0
    If MultSpin.Value = -1 Then MultSpin.Value = 23
  Else
    'even number
    If MultSpin.Value = 60 Then MultSpin.Value = 0
    If MultSpin.Value = -1 Then MultSpin.Value = 59
  End If
  UserForm2.Controls("TXT" & n).Value = Format(MultSpin.Value, "00")
End Sub

3. Verify that the class module name is Class1:
View attachment 89649


4. Change UserForm2 to the name of your userform on this line of the class module.

UserForm2.Controls("TXT" & n).Value = Format(MultSpin.Value, "00")

5. According to your example. Spinbutton1 corresponds to TXT1, spinbutton2 to TXT2, and I guess spinbutton3 to TXT3 and so on.
I was also able to deduce that the 1 is for the hours and the 2 is for the minutes, so I also suppose that the 3 is for the hours and the 4 for the minutes, the 5 for hours and the 6 for the minutes, so the odd numbers are for the hours and the even for the minutes. If that pattern is correct, then the code in the class module is correct.

6. Now put the following code in the Initialize event of your userform:

Dim SpinBt() As New Class1      '<---At the start of all code

Private Sub UserForm_Initialize()
   Dim i As Long
   ReDim SpinBt(1 To 30)    'change to 30
   For i = 1 To UBound(SpinBt)
      Set SpinBt(i).MultSpin = Me.Controls("SpinButton" & i)
   Next i
End Sub

7. Run your userform and verify the functionality of the spinbuttons and textboxes.
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

Thank you! Exactly what I was looking for!