Saturday, April 10, 2010

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.

Add Spin Buttons Active X Excel

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

Add Spin Buttons Active X Excel



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

1 comment:

  1. Dear Yogesh brother,Your posts are really qualitative and very useful.I'm not able to change Spin button name as per above directions. Could you help me in resolve the issue.

    ReplyDelete