VBA macros: make one spinner do the work of many
Helen Bradley shows how to make an Excel spin button do the work of multiple spin buttons.
Spin button controls let your user click a button to change a value. They are typically used in user forms but, because they are also part of the ActiveX Controls collections you can also place them on a worksheet. When you have multiple cells you want to manage with spin buttons the worksheet can become crowded and hard to manage. The simpler solution is to make a single spin button do all the work. We’ll show you how.
Typically a spin button on a worksheet is linked to a cell and it has some limits in terms of the values it can provide. If you bypass these settings and simply use the spin button events to trap the user interaction then you can make it do whatever you want it to do.
THE MORTGAGE CALCULATOR
To see this at work, we will create a simple mortgage payment calculator. The initial values and labels are type in cells B3:C5 and cell B7. Type this formula in cell C7 to make the mortgage payment calculation:
= PMT(C4/12,C5*12,C3)*-1
We’ll use a spin button to change values in the cells C3, C4 & C5. To add this, click the Developer tab, click ‘Design Mode’, and from the Insert list click the ‘Spin Button (Active X control)’ and drag a spin button onto the worksheet. Right click the control and choose ‘View Code’ to open the code dialog. Add this code to the code dialog:
Function Valid Selection (Range1 As Range, Range2 As Range) As Boolean
Valid Selection= Not (Application. Intersect( Range 1, Range2) Is Nothing)
End Function
Private Sub Spin Button 1_ SpinDown()
If Valid Selection (Active Cell, Range(“C3:C5”)) Then
If Active Cell. Address = “$C$3” Then ‘decrement $1000
ActiveCell.Value = Active Cell.Value - 1000
ElseIf ActiveCell. Address = “$C$4” Then ‘ decrement .25%
ActiveCell.Value = ActiveCell.Value - 0.0025
Else ‘ decrement 1 year ActiveCell.Value = ActiveCell.Value - 1 End If If ActiveCell.Value < 0 Then ActiveCell.Value = 0 End If End Sub
Private Sub SpinButton1_ SpinUp()
If ValidSelection(ActiveCell, Range(“C3:C5”)) Then
If ActiveCell.Address = “$C$3” Then ‘increment $1000
ActiveCell.Value = ActiveCell.Value + 1000
ElseIf ActiveCell. Address = “$C$4” Then ‘ increment .25%
ActiveCell.Value = ActiveCell.Value + 0.0025
Else ‘ increment 1 year
Download the macros for this story at www.apcmag.com/magstu .
ActiveCell.Value = ActiveCell.Value + 1 End If End If End Sub
Disable Design Mode, save the worksheet as a macro enabled worksheet and test the spin button. When the active cell is outside the range C3:C5 nothing happens when you click the spin button. If either cell C3, C4 or C5 is active then depending on whether the up or down arrow is selected, the value in that cell is incremented or decremented by 1000 for the loan amount, 0.25% for the interest rate and 1 for the term of the loan. If any value falls below zero it is reset to 0.