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..
This works nicely; brute force but effective. You need to continue the code to cover all 14 txtDirector fields.
Private Sub txtDirectorsAsk_Change()
Call subDisableAllDirectors
Select Case txtDirectorsAsk.Value
Case 1
txtDirector1.Visible = True
Case 2
txtDirector1.Visible = True
txtDirector2.Visible = True
Case 3
txtDirector1.Visible = True
txtDirector2.Visible = True
txtDirector3.Visible = True
Case 4
txtDirector1.Visible = True
txtDirector2.Visible = True
txtDirector3.Visible = True
txtDirector4.Visible = True
Case 5
txtDirector1.Visible = True
txtDirector2.Visible = True
txtDirector3.Visible = True
txtDirector4.Visible = True
txtDirector5.Visible = True
Case 6
txtDirector1.Visible = True
txtDirector2.Visible = True
txtDirector3.Visible = True
txtDirector4.Visible = True
txtDirector5.Visible = True
txtDirector6.Visible = True
End Select
End Sub
Sub subDisableAllDirectors()
txtDirector1.Visible = False
txtDirector2.Visible = False
txtDirector3.Visible = False
txtDirector4.Visible = False
txtDirector5.Visible = False
txtDirector6.Visible = False
txtDirector7.Visible = False
txtDirector8.Visible = False
txtDirector9.Visible = False
txtDirector10.Visible = False
txtDirector11.Visible = False
txtDirector12.Visible = False
txtDirector13.Visible = False
txtDirector14.Visible = False
End Sub
UpRider
Hello All, I’ve created a userform through which i enter data into my worksheet. Right now I’m able to enter data and have submitted to their respective cells in worksheet. There is also a spin button which works fine, but wat i want is to find data through spin button and edit those existing data and save it (overwrite it) without creating new one.
Please help in regards to the same as I’m totally stuck with the issue.
May be my codings are not up to the mark….!!!!
Dear Dick,
Made a lot of excel VBA-apps, but now for the 1st time in need of spinbutton/textbox-combination.
Simple code, but I miss the point regarding and logic behind disabling and enabling events for both controls.
Can you please explain a bit?
Kind regards, Hans Troost
Sure Hans. It would cause an endless loop without it. Change the textbox and the Textbox_Change event changes the spin button to match. That change triggers the SpinButton_Change event, which changes the textbox. That change triggers the Textbox_Change event, and on and on.