Excel Macro Tutorial : VBA Spin Button

December 17, 2011| Posted by : | Filled in Excel Macro, Excel Macro Tutorial

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.


Add Spin Button to Excel

Add Spin Button to Excel




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:


Working with Spin Button

Working with Spin Button




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

Comments

comments

Related posts:

  1. Excel Macro Tutorial : How to add different types of controls like Text Box, Radio button, button etc. in Excel Dear Readers, This is the 3rd Article of this Excel...
  2. Excel Macro Tutorial : VBA Control Property In last post Excel Macro Tutorial : Visual Basic Editor...
  3. Excel Macro Tutorial : How to write Excel Macro – Your First Excel Macro Dear Readers,   Till now we have learnt following things...

Subscribe To Get New Posts delivered directly to your Mailbox

One Response to “Excel Macro Tutorial : VBA Spin Button”

  1. [...] How to use of Spin Button in Excel [...]

Leave a Reply

  • About
  • Downloads
  • Excel Formula
  • Excel Tips
  • Excel Macro Tutorial
  • VBA Functions
  • Archives
  • Contact
  • Excel Formula
  • Excel Functions
  • Excel Macro
  • Excel Macro Basics
  • Excel Macro Tutorial
  • Excel Tips
  • HP QC
  • Interesting VBA Functions
  • New to Excel Macro ?
  • Personal
  • Popular Articles
  • 2014
  • 2013
  • 2012
  • 2011