Userforms: SpinButton Control
The SpinButton control can be used to increment a number, both and up down. You can set the Min and Max properties to limit the range. The defaults for these properties are 0 and 100, but these can be changed. Help recommends that you don’t exceed -32767 to +32,767, which is the range for an Integer data type. The properties actually accept the Long data type, which is much larger.
The SmallChange property determines how much the Value changes with each click. The default is 1.
Min, Max, and SmallChange all take Long Integers, which means no decimals. If you need decimals, then you have to scale the SpinButton up and do some math. That’s possible because the SpinButton does not display a value, you have to use a TextBox, or some other control, to show the value of the SpinButton. For instance, if you want to increment by 0.5 from 1 to 10, you need to set your Max to 20, then code the TextBox to display the SpinButton’s value divided by two.
Here’s an example that links a SpinButton and TextBox via code. The user can increment the value with the SpinButton or type a number into the TextBox.
Dim mbEvents As Boolean
Private Sub SpinButton1_Change()
mbEvents = True ’stop events from running
Me.TextBox1.Text = Me.SpinButton1.Value ’sync the textbox
mbEvents = False ‘events to run again
End Sub
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not mbEvents Then ‘if events are not off
mbEvents = True ‘turn events off
If IsNumeric(Me.TextBox1.Text) Then ‘no text allowed
If Val(Me.TextBox1.Text) > Me.SpinButton1.Max Or _
Val(Me.TextBox1.Text) < Me.SpinButton1.Min Then
Cancel = True ‘if the typed in value is not valid
‘don’t update the textbox
Else
‘if all okay, sync the spinbutton
Me.SpinButton1.Value = Me.TextBox1.Text
End If
Else
Cancel = True ‘if text, don’t update the textbox
End If
mbEvents = False ‘events to run again
End If
End Sub
Private Sub UserForm_Initialize()
mbEvents = True ‘turn events off
Me.TextBox1.Text = Me.SpinButton1.Value ‘Initialize the textbox
mbEvents = False ‘events to run again
End Sub
Well I’m looking for a code that links a spinbutton with frame or dsomething like that If you can help me I’m looking forward to it. Thanks any way.
I am trying to us a spin button to navagate through 20 worksheets and display them one at a time on the first Worksheet named fixture and each worksheet is named “Round n”.
I have been using this code
Private Sub SpinButton1_SpinDown()
Worksheets(”Round 1″).Range(”A1:G19″).Copy
ActiveSheet.Paste Destination:=Worksheets(”Fixture”).Range(”A2:G19″)
End Sub
Private Sub SpinButton1_SpinUp()
Worksheets(”Round 20″).Range(”A1:G19″).Copy
ActiveSheet.Paste Destination:=Worksheets(”Fixture”).Range(”A2:G19″)
End Sub
I cannot see in any of excel help files how to use next position or increment position like in C#.
I hope you can help
David
David, you will have to use the value of the spinbutton to point to the correct worksheet to copy from. Something like this…
Private Sub SpinButton1_SpinDown()
Worksheets(”Round ” & Cstr(spinbutton1.value)).Range(”A1:G19″).Copy
ActiveSheet.Paste Destination:=Worksheets(”Fixture”).Range(”A2:G19″)
End Sub
You’ll need to set the spinbutton value and limit it’s range from 1 to 20 for that to work too… Set the min property to 1, and the max property to 20 to achieve this.
hth
mur
I’d love to understand what you were doing in that code..