How to use spin buttons in Excel VBA

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.

3. Drag a spin button on your worksheet.
4. Right click the spin button (make sure Design Mode is selected).
5. Click 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:

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.