Add Spin Buttons Active X Excel

In my previous posts I have shared EMI Calculator with you. This was mode using Excel PMT function and Spin Buttons ActiveX Controls in Excel. In this post I am sharing how to add spin buttons ActiveX controls to excel worksheet.



Step 1 Have Developer Tab visible in your Excel 2007. In case you already have it proceed to step 2
Step 2 Click on Insert under controls group
Step 3 Click on Spin Buttons under ActiveX Controls
Step 4 Draw Spin Button on your worksheet.
Step 5 Rename the Spin Button Object
Step 6 Add VBA code to the Spin Button

Checkout Screen Cast Below

In this screen cast we have added spin button next to loan amount and renamed the spin button object as Loan

Similarly add two more spin buttons next to Interest Rate and Years. Rename them as Interest and Years respectively.

To make these buttons work we need to add VBA code. Separate macros are required for each button that is why we have renamed the spin buttons. It is to give them a name that can be related later on by us when we look at the macro code. Macro code will be using the names we have given to them

Now to add the VBA code , double click on the spin button object, it will open VBA editor. Copy following VBA code to worksheet module

Private Sub Loan_SpinDown() With Range("C4") .Value = WorksheetFunction.Max(100000, .Value - 100000) End With End Sub Private Sub Loan_SpinUp() With Range("C4") .Value = WorksheetFunction.Min(10000000, .Value + 100000) End With End Sub Private Sub Interest_SpinDown() With Range("C5") .Value = WorksheetFunction.Max(0.03, .Value - 0.0025) End With End Sub Private Sub Interest_SpinUp() With Range("C5") .Value = WorksheetFunction.Min(0.25, .Value + 0.0025) End With End Sub Private Sub Years_SpinDown() With Range("C6") .Value = WorksheetFunction.Max(1, .Value - 1) End With End Sub Private Sub Years_SpinUp() With Range("C6") .Value = WorksheetFunction.Min(25, .Value + 1) End With End Sub

With this your EMI Calculator with Spin Buttons ActiveX Controls is ready

Visit EMI Calculator page if you want to download excel file complete with macros and Spin Buttons Activex controls