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
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
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
