Spin Button

 

A spin button can be used to increment a number in a cell. To create a spin button in Excel VBA, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Spin Button.

Create a spin button in Excel VBA

3. Drag a spin button on your worksheet.

4. Right click the spin button (make sure Design Mode is selected).

5. Click View Code.

View Code

Note: you can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now, we will leave SpinButton1 as the name of the spin button.

6. To link this spin button to a cell, add the following code line.

Range("C3").Value = SpinButton1.Value

7. You can set a maximum and minimum by adding the following code lines.

SpinButton1.Max = 100
SpinButton1.Min = 0

8. To change the incremental value, use the SmallChange property.

SpinButton1.SmallChange = 2

9. Click the arrows of the spin button (make sure Design Mode is deselected).

Result:

Spin Button

Note: instead of changing the properties of the spin button at runtime, you can also change the properties at design-time. To achieve this, right click on the spin button (make sure Design Mode is selected) and click on properties.

Properties

7/8 Completed! Learn more about activex controls >
Back to Top: Spin Button   |   Go to Next Chapter: Userform