[VBA] Mastering Spin Buttons on Excel Sheets | A Guide to Key Properties

2025年12月29日

Introduction

If you want to allow users to easily input numbers on an Excel sheet, the Spin Button (Form Control) is very convenient.

Since users can increase or decrease numbers simply by clicking the up and down arrows with a mouse, it prevents typing errors and provides an intuitive experience.

To control this Spin Button with VBA, it is essential to understand its basic properties. This article explains the five most important properties for handling Spin Buttons on a sheet, with sample code.

Max: Maximum value
Min: Minimum value
SmallChange: Amount of change per click
LinkedCell: The cell linked to the button
Value: Current value

Spin Button Properties and Sample Code

The following sample code sets various properties for a Spin Button named ItemCountSpinner.

Complete Code

' Configure Spin Button Properties
Sub ConfigureSpinner()
    Dim targetSpinner As Spinner
    
    ' Get the spin button named "ItemCountSpinner"
    Set targetSpinner = ActiveSheet.Spinners("ItemCountSpinner")
    
    With targetSpinner
        ' .Min property: Set the minimum allowable value
        .Min = 0
        
        ' .Max property: Set the maximum allowable value
        .Max = 100
        
        ' .SmallChange property: Set the amount of change for one click
        .SmallChange = 5
        
        ' .Value property: Set the current value
        .Value = 20
        
        ' .LinkedCell property: Set the cell to display/link the value
        .LinkedCell = "D2"
    End With
    
    ' Check the current value
    MsgBox "Current Spin Button Value: " & targetSpinner.Value, vbInformation

End Sub

1. Min / Max Properties

These specify the minimum and maximum integers that can be set for the Spin Button. Values outside this range cannot be set.

2. SmallChange Property

This specifies the integer amount by which the value increases or decreases when the user clicks the up or down arrow once.

3. LinkedCell Property

This specifies the address of the cell linked to the Spin Button’s current value as a string (e.g., “D2”). The specified cell will always display the Spin Button’s current value. If the user directly enters a number into the cell, the Spin Button’s value will update (provided it is within the Min/Max range).

4. Value Property

This gets or sets the current value of the Spin Button. If .LinkedCell is set, the value of this property will always match the value of the linked cell.

Summary

In this article, I explained five basic properties for manipulating Spin Buttons on a sheet using VBA.

By combining these properties, you can build a safer and easier-to-use input interface by limiting the range of numbers users can input or adjusting the increment steps.