How to Create Spin Button with VBA in Excel (3 Useful Methods)

When we work in Excel, we often need to change some values in an increasing or decreasing manner. But it gets very monotonous to type each time. Therefore, Microsoft Excel has introduced the Spin Button to operate the control easily. In this article, we will demonstrate how to create a Spin Button with VBA in Excel with 3 useful methods.

What Is Spin Button in Excel?

The Spin Button is a form of control in Excel that comprises two arrows. These arrows allow to increase or decrease values of the linked cell. Both the buttons are located beside each other but one works for increasing and the other works for decreasing numeric values. The Spin Button can be either horizontal or vertically directional.

Now we will illustrate the methods to create a Spin Button. For this, here is a sample dataset for House Rent Calculator. It has the information on Monthly Rent, Community Bill and Council Tax in the Cell range B4:C6.

To get the Total Rent, insert this formula in Cell C7.

=SUM(C4:C6)

Here, the SUM function calculates the total amount of the Cell range C4:C6.

The values will differ according to your current region. Therefore, we created a template to calculate house rent. Now instead of typing each value and calculating the total, we will insert a Spin Button to automatically do the task.

1. Creating Spin Button with Form Controls in Excel

This first method will discuss the process of creating a Spin Button with Form Controls. Let’s see how it works.

First, go to the Developer tab and select Insert.
Here, choose Spin Button from the Form Controls.

Then, drag your cursor on the worksheet to insert a Spin Button like this.

Now, right-click on it and choose Format Control from its Context Menu.

Afterward, go to the Control tab and enter the values as shown below.

Along with this, link Cell C4 in the Cell link box and press OK.

Next, right–click on the Spin Button again and select Copy and paste beside Cells C5 and C6.

Now, make the following changes in the Format Control window for Cell C5 and press OK.

Similarly, insert the following values in the Format Control window for Cell C6 and hit OK.

Finally, you can simply spin the numbers to calculate Total Rent like this.

2. Using ActiveX Controls to Insert Spin Button

ActiveX Controls also has the Spin Button tool in Excel. Follow the steps below to insert and activate it.

In the beginning, select Insert from the Developer tab.
In the drop-down menu, choose Spin Button under the ActiveX Controls section.

Then, drag and draw a Spin Button on the worksheet like this.

Now, click on Properties under the Controls section of the Developer tab.

In the Properties window, type C4 in the LinkedCell box.
Along with it, insert the Max and Min values which will determine the range of changing values.
Also, insert a value in the SmallChange box to determine the interval of every spin.

Next, insert the 2nd Spin Button beside Cell C5 and insert the following values in its Properties window.

Lastly, insert the 3rd Spin Button beside Cell C6 and define the following parameters in the Properties window.

You can also find the Properties command by right-clicking on the Spin Button.

Finally, turn off the Design Mode in the Developer tab.

That’s it, you can now operate the Spin Buttons like this with your preferred values.

Moreover, if Cell C4, C5, and C6 have similar Max, Min, and SmallChange values, then instead of creating individual Spin Button, you can create only one.
Then, right–click on it and select View Code.

Following this, type code > save and close the VBA Editor > operate the Spin Button to change values all at once.

Private Sub SpinButton_Change()
Range("C4").Value = SpinButton.Value
Range("C5").Value = SpinButton.Value
Range("C6").Value = SpinButton.Value
SpinButton.Max = 30000
SpinButton.Min = 0
SpinButton.SmallChange = 1
End Sub

3. Designing UserForm to Create Spin Button in Excel

In this last method, let us design a UserForm in the VBA Editor to create a Spin Button. To do the task, follow the process below.

First, select Visual Basic under the Code section of the Developer tab.

Then, select UserForm in the Insert tab of the Microsoft Visual Basic for Applications window.

Now, drag and draw a TextBox and a SpinButton from the Toolbox inside the UserForm window.

Next, right–click on TextBox and select View Code from its Context Menu.

Then, type this code inside the Code window.

Private Sub SpinButton1_Change()
UserForm1.TextBox1.Text = SpinButton1.Value
End Sub
Private Sub TextBox1_Change()
Range("C4") = UserForm1.TextBox1.Text
End Sub
Private Sub UserForm_Click()
SpinButton1.Value = 0
End Sub

In this code, Range(“C4”) in the cell that we are linking with the SpinButton1.

Following this, click on the RunSub button on the top or press F5 on your keyboard.

Finally, you will get the UserForm dialogue box where you can operate the Spin Button to insert values in Cell C4.

Follow the same procedure for Cell C5 and C6 and calculate the Total Rent.

Things to Remember

In the case of UserForm, if you change the name of the SpinButton and TextBox from its Properties window, you have to give the same name in the code.
If you create a Spin Button with Form Controls, you have to right-click on it and then move or resize it with the left-click.
When you insert the Spin Button with the ActiveX Controls, turn on the Design Mode to move or resize it.

Download Practice Workbook

Download the practice workbook from the link below.

Spin Button VBA.xlsm

Conclusion

Finally, we are at the end of our long tutorial. Here we described how to create Spin Button with VBA in Excel with 3 Useful Methods. Let us know your insightful suggestion on this. Keep learning Excel with ExcelDemy.

Get FREE Advanced Excel Exercises with Solutions!