How to Add and Format a Spinner Button in Excel

Spinner buttons, also known as spin buttons, are useful tools in Excel for incrementing or decrementing a value in a cell with just a click. This guide will walk you through the process of adding and formatting a spinner button in Excel using two different methods.

Method 1: Using the Developer Tab

Step 1: Enable the Developer Tab

1. Open Excel and go to the File tab.

2. Select Options from the left-hand menu.

3. In the Excel Options dialog box, click on Customize Ribbon.

4. On the right side, check the box for Developer under the Main Tabs section.

5. Click OK to save the changes. The Developer tab should now be visible in the ribbon.

Step 2: Insert the Spinner Button

1. Click on the Developer tab.

2. In the Controls group, click on Insert.

3. From the dropdown menu, select the Spin Button (Form Control).

4. Click and drag on your worksheet to draw the spinner button.

Step 3: Link the Spinner Button to a Cell

1. Right-click on the spinner button and select Format Control.

2. In the Format Control dialog box, go to the Control tab.

3. Set the Current value, Minimum value, Maximum value, and Incremental change according to your needs.

• Current value: The starting value for the spinner.
• Minimum value: The lowest value the spinner can go to.
• Maximum value: The highest value the spinner can reach.
• Incremental change: The amount by which the value changes when you click the spinner.

4. In the Cell link box, enter the reference for the cell you want to control with the spinner (e.g., A1).

5. Click OK to apply the settings.

Step 4: Test the Spinner Button

1. Click the up or down arrow on the spinner button to see the linked cell value change.

2. Adjust the settings as needed by right-clicking the spinner button and selecting Format Control again.

download the practice sheet used in this article

Download

Method 2: Using VBA Code

Step 1: Enable the Developer Tab

Follow the same steps as in Method 1 to enable the Developer tab.

Step 2: Insert the Spinner Button

1. Click on the Developer tab.
2. In the Controls group, click on Insert.
3. From the dropdown menu, select the Spin Button (ActiveX Control).
4. Click and drag on your worksheet to draw the spinner button.

Step 3: Access the VBA Editor

1. With the spinner button selected, click on View Code in the Controls group.
2. This will open the Visual Basic for Applications (VBA) editor.

Step 4: Write the VBA Code

1. In the VBA editor, you will see a default subroutine for the spinner button.

2. Write the following code inside the subroutine to link the spinner button to a cell (e.g., A1):

Private Sub SpinButton1_Change()
    Range("A1").Value = SpinButton1.Value
End Sub

3. Close the VBA editor to return to your worksheet.

Step 5: Configure the Spinner Button Properties

1. Right-click on the spinner button and select Properties.
2. In the Properties window, set the Min property to the minimum value you want.
3. Set the Max property to the maximum value you want.
4. Close the Properties window.

Step 6: Test the Spinner Button

1. Click the up or down arrow on the spinner button to see the linked cell value change.
2. Adjust the properties as needed by right-clicking the spinner button and selecting Properties again.

Tips for Formatting Spinner Buttons

Adjusting Size and Position

• Size: Click and drag the edges or corners of the spinner button to resize it.
• Position: Click and drag the spinner button to move it to the desired location on your worksheet.

Changing Spinner Button Appearance

1. Right-click on the spinner button and select Format Control (for Form Control) or Properties (for ActiveX Control).
2. In the Format Control dialog box, you can change the button’s font, alignment, and colors.
3. For ActiveX Controls, the Properties window allows more customization options such as font size, background color, and border style.

Using Multiple Spinner Buttons

• You can add multiple spinner buttons to control different cells or ranges. Repeat the steps for each spinner button and set different cell links or VBA codes accordingly.

Combining with Other Controls

• Spinner buttons can be used in combination with other form controls like combo boxes or checkboxes to create interactive and user-friendly interfaces.