In VBA, you can create a Spin button which allows a user to increment a number in the cell in a defined range. Every time a user clicks on a button, the number will increase or decrease. In this tutorial, you will learn how to create a Spin button and use it in Excel and VBA.
If you want to learn how to use an Option button, click on this link: Option button Excel VBA
Create a Spin Button
In order to insert a Spin button in the Worksheet, you need to go to the Developer tab, click Insert and under ActiveX Controls choose Spin button:
Image 1. Insert a Spin button in the Worksheet
When you select the Spin button which you inserted, you can click on Properties under the Developer tab:
Image 2. Change Spin button Properties
Here you can set different properties of the Spin button. For the beginning, we changed the attribute Name to spbSpinButton. Now, we can use the Spin button with this name in VBA code. Other important attributes are Min and Max, which define a number range and SmallChange which defines a step of incrementation.
Set a Spin Button Using VBA
We will first see how to set properties of a Spin button in VBA and get a value in Worksheet. The code needs to be in event Change of the object spbSpinButton. You can enter this event by right-clicking on the Spin button and choosing View Code option. Here is the code:
Private Sub spbSpinButton_Change() Sheet1.spbSpinButton.Min = 100 Sheet1.spbSpinButton.Max = 200 Sheet1.spbSpinButton.SmallChange = 10 Sheet1.Range("B2") = Sheet1.spbSpinButton.Value End Sub
First, we set the lower limit for number range:
Sheet1.spbSpinButton.Min = 100
After that, we set the upper limit for number range:
Sheet1.spbSpinButton.Max = 200
We also need to set the step for number incrementation:
Sheet1.spbSpinButton.SmallChange = 10
Finally, we are assigning the current value of the Spin button to the cell B2. This value is in the Value attribute of the object Sheet1.spbSpinButton:
Sheet1.Range("B2") = Sheet1.spbSpinButton.Value
Now, whenever we click on the Spin button, the value will increase or decrease by 10 in the range 100-200:
Image 3. Increase a number using the Spin button in VBA
Set a Spin Button in Excel
Another way to set a Spin button is using the Properties. Click on Properties under the Developer tab:
Image 4. Set a Spin button in Excel
Here we can set all the attributes we want: Min is 10, Max is 100 and SmallChange is 2. If you want to put the result of the Spin button in the cell B2, you have to put this cell in attribute LinkedCell.
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!