{"id":12110,"date":"2011-12-17T20:06:36","date_gmt":"2011-12-17T20:06:36","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=1076"},"modified":"2011-12-17T20:06:36","modified_gmt":"2011-12-17T20:06:36","slug":"use-of-spin-button-in-excel","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2011\/12\/use-of-spin-button-in-excel\/","title":{"rendered":"Excel Macro Tutorial : VBA Spin Button"},"content":{"rendered":"
In the previous Article How to write Excel Macro – Your First Excel Macro<\/a><\/strong> of <\/a>Excel Macro Tutorial<\/strong>, 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. Step 1.<\/strong>Go to Developer’s Tab (How to Add Developer’s Add in to the Ribbon<\/a>) Add Spin Button to Excel<\/p><\/div> Step 1.<\/strong>Double Click on the Control. Working with Spin Button<\/p><\/div> <\/p>\n 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.<\/strong> <\/p>\n
\n<\/p>\n
\n
\nSpin Button:<\/strong> 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.
\n
\n<\/a>
\n<\/p>\nHow to Add VBA Spin Button in your Excel Sheet<\/h3>\n
\nStep 2.<\/strong>Go to to Controls Section -> Click on Arrow on INSERT button
\nStep 3.<\/strong>Click on ActiveX Spin Button
\nStep 4.<\/strong>Now Drag the Place where you want to Place that button and re-size as per your requirement.<\/strong><\/strong><\/strong>
\n
\n<\/a>
\n
\nNow 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.
\n<\/p>\nHow to Use VBA Spin Button in your Excel Sheet<\/h3>\n
\nStep 2.<\/strong>You are taken to the Visual Basic Editor of the Excel
\nStep 3.<\/strong>Here Select the Name of your Control.
\nStep 4.<\/strong>Now you Select SpinUp<\/strong> and SpinDown<\/strong>Events from the Even Drop down as shown below in image:
\n
\n<\/a>
\n
\nStep 5.<\/strong>Now Copy paste the below Code for your SpinUp Event and SpinDown Event
\n<\/p>\nSpinUp Event<\/h2>\n
\nPrivate Sub SpinButton1_Spinup()\n With Range(\"A1\")\n .Value = WorksheetFunction.Min(150, .Value + 10)\n End With\nEnd Sub<\/code><\/pre>\n
SpinDown Event<\/h2>\n
Private Sub SpinButton1_Spindown()\n With Range(\"A1\")\n .Value = WorksheetFunction.Max(50, .Value - 10)\n End With\nEnd Sub<\/code><\/pre>\n
\n
\nThis way you can customize it as per your requirement.<\/p>\nIf you face any issue or doubt about this feel free to contact me on info@learnexcelmacro.com<\/h2>\n