How to manipulate Spin Control?

[DreyFox]

Hello, I have something I would like to do using spin control. So I'd like to have the spin control start counting at RH00000-00. Then when the hit the arrow key to the right, it becomes RH00001-00. Then when I hit it to the right again, it becomes RH00001-01. Is this doable with some VBA? Currently, I have the VBA code to do just basic RH0, RH1, etc. Any help would be greatly appreciated! Thank you!

SpinButton1.SmallChange = 1
TextBox8.Text = "RH" & SpinButton1.Value

 

[AlphaFrog]

SpinButton1.SmallChange = 1
TextBox8.Text = "RH" & Format(SpinButton1.Value, "00000-00")

 

[EXCEL MAX]

You can do something like this...

Option Explicit

 Dim varString As String
 Dim varNClick, varNClick2
    
 Private Sub UserForm_Initialize()
 
    MsgBox ("RH00000-00")
    varNClick = 0
    varNClick2 = 0
    
End Sub

Private Sub SpinButton1_SpinUp()

    If varNClick = varNClick2 Then
        varNClick = varNClick + 1
    Else
        varNClick2 = varNClick2 + 1
    End If
EX:
    varString = "RH" & Format(varNClick, "00000") & "-" & _
                           Format(varNClick2, "00")
    MsgBox (varString)

End Sub

 

[DreyFox]

You can do something like this...

Option Explicit

Dim varString As String
Dim varNClick, varNClick2
   
Private Sub UserForm_Initialize()

    MsgBox ("RH00000-00")
    varNClick = 0
    varNClick2 = 0
   
End Sub

Private Sub SpinButton1_SpinUp()

    If varNClick = varNClick2 Then
        varNClick = varNClick + 1
    Else
        varNClick2 = varNClick2 + 1
    End If
EX:
    varString = "RH" & Format(varNClick, "00000") & "-" & _
                           Format(varNClick2, "00")
    MsgBox (varString)

End Sub

Is there a way to do this with ActiveX instead of UserForm?

 

[DreyFox]

SpinButton1.SmallChange = 1
TextBox8.Text = "RH" & Format(SpinButton1.Value, "00000-00")

This only increases the 2nd term 00 into the hundreds before I get 00001. I'd like to get 00000-> 00001 then 00 -> 01

 

[EXCEL MAX]

Yes, but take care where you will put initial values.
It may be event "Workbook_Open" or Worksheet_Activate instead UserForm_Initialize.
Example...

Option Explicit

Dim varString As String
Dim varNClick, varNClick2
 
Private Sub SpinButton1_SpinUp()

    If varNClick = varNClick2 Then
        varNClick = varNClick + 1
    Else
        varNClick2 = varNClick2 + 1
    End If
    varString = "RH" & Format(varNClick, "00000") & "-" & _
                           Format(varNClick2, "00")
    MsgBox (varString)
    
End Sub

Private Sub Worksheet_Activate()
    
    MsgBox ("RH00000-00")
    varNClick = 0
    varNClick2 = 0
    
End Sub

 

[DreyFox]

Yes, but take care where you will put initial values.
It may be event "Workbook_Open" or Worksheet_Activate instead UserForm_Initialize.
Example...

Option Explicit

Dim varString As String
Dim varNClick, varNClick2

Private Sub SpinButton1_SpinUp()

    If varNClick = varNClick2 Then
        varNClick = varNClick + 1
    Else
        varNClick2 = varNClick2 + 1
    End If
    varString = "RH" & Format(varNClick, "00000") & "-" & _
                           Format(varNClick2, "00")
    MsgBox (varString)
   
End Sub

Private Sub Worksheet_Activate()
   
    MsgBox ("RH00000-00")
    varNClick = 0
    varNClick2 = 0
   
End Sub

Also, is it possible for the numbers to be displayed using a TextBox instead of a message box? Would I just do TextBox1.Text = varString?

 

[AlphaFrog]

Maybe this...

With SpinButton1
    .SmallChange = 1
    TextBox8.Text = "RH" & Format(.Value / 2, "00000") & Format(Application.Max((.Value - 1), 0) / 2, "-00")
End With

 

[DreyFox]

Maybe this...

With SpinButton1
    .SmallChange = 1
    TextBox8.Text = "RH" & Format(.Value / 2, "00000") & Format(Application.Max((.Value - 1), 0) / 2, "-00")
End With

This works, sorry for the delay, I forgot to mark as new, as I'm still kind of new to this.