In the previous Article How to write Excel Macro – Your First Excel Macro of Excel Macro Tutorial, you have seen how to write Excel Macro. Also in Previous Tutorials, you have seen all the basic concept of Excel Macro.

In this Article I am going to show you how to Add and Use Spin Button.

Spin Button: Spin button is an ActiveX Control which have Two Buttons Up and Down. Both the buttons always remain together but function separately. Generally you might have seen this button in many dialog box where user wants to increase or decrease values in a Text box or anywhere by clicking on Up or Down button correspondingly.
 

How to Add VBA Spin Button in your Excel Sheet

Step 1.Go to Developer’s Tab (How to Add Developer’s Add in to the Ribbon)
Step 2.Go to to Controls Section -> Click on Arrow on INSERT button
Step 3.Click on ActiveX Spin Button
Step 4.Now Drag the Place where you want to Place that button and re-size as per your requirement.

Now you got Added Spin Button to your Excel Sheet. Now we will learn how to make this button working by writing a simple line of code.

How to Use VBA Spin Button in your Excel Sheet

Step 1.Double Click on the Control.
Step 2.You are taken to the Visual Basic Editor of the Excel
Step 3.Here Select the Name of your Control.
Step 4.Now you Select SpinUp and SpinDownEvents from the Even Drop down as shown below in image:

Step 5.Now Copy paste the below Code for your SpinUp Event and SpinDown Event

SpinUp Event

Private Sub SpinButton1_Spinup()
    With Range("A1")
        .Value = WorksheetFunction.Min(150, .Value + 10)
    End With
End Sub

SpinDown Event

Private Sub SpinButton1_Spindown()
    With Range("A1")
        .Value = WorksheetFunction.Max(50, .Value - 10)
    End With
End Sub

in Above Example, Spin button will increase or decrease the Value of Cell A1 by 10 on clicking on Up and Down button respectively. Also down button will not decrease the Value less than 50 and also Up button will not allow to increase for more than 150.

This way you can customize it as per your requirement.

If you face any issue or doubt about this feel free to contact me on info@learnexcelmacro.com

To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial