Add an ActiveX SpinButton Control to a Spreadsheet
One of my favorite tools for controlling the input to a single cell within an interactive spreadsheet model is an ActiveX SpinButton. When added to a spreadsheet, the SpinButton can allow the user to move quickly through multiple scenarios within a predefined range of values.
In Excel 2007, you can add an ActiveX SpinButton to a spreadsheet by first selecting the Developer tab. Then select Insert -> ActiveX Controls -> Spin Button.
In Design mode, you can link a cell as well as adjust the minimum and maximum values for the SpinButton. To do so, click on the SpinButton when in design mode. Then right-click on your mouse to access the properies for that SpinButton.
Rather than manually entering the minimum, maximum, and linked cell properties, my personal preference is to use a few simple VBA statements. For example, assuming the SpinButton is named "SpinButton1", to add a maximum value = 30, a minimum value = 20, and a linked cell = C4, and an incremental value = 1, add the following code to the sheet module for the sheet containing the ActiveX SpinButton.
Private Sub SpinButton1_SpinDown()
With Range("C4")
.Value = WorksheetFunction.Max(20, .Value - 1)
End With
End Sub
Private Sub SpinButton1_SpinUp()
With Range("C4")
.Value = WorksheetFunction.Min(30, .Value + 1)
End With
End Sub
The cell entry for the SpinButton combined with the SpinButton Control might be set up on the spreadsheet to look like this:
To allow the SpinButton to accept changes in percent, slightly modify the code above. For example, to add a maximum value = .50, a minimum value = .30, and a linked cell = C4, and an incremental value = .01, add the following code to the sheet module for the sheet containing the ActiveX SpinButton.
Private Sub SpinButton1_SpinDown()
With Range("C4")
.Value = WorksheetFunction.Max(0.3, .Value - 0.01)
End With
End Sub
Private Sub SpinButton1_SpinUp()
With Range("C4")
.Value = WorksheetFunction.Min(0.5, .Value + 0.01)
End With
End Sub
After changing the cell entry number format to percent, for the SpinButton combined with the SpinButton Control might be set up on the spreadsheet to look like this:
Although you must be in design mode to modify the size and position of the SpinButton and /or modify its properties, you must exit the design mode in order for the SpinButton to be operable.
Note: the examples above are based on code and techniques that can be found at the following:
- From java2s.com: SpinUp Events
- From java2s.com: SpinDown Events
- From Microsoft: Add a Scroll Bar or Spin Button to a Worksheet
- Excel 2007 Power Programming with VBA by John Walkenbach - Chapter 13 - Pages 440 - 442.
- Excel 2000 VBA by John Green - Chapter 10 - page 175.
John,
You can simulate this approach without VBA. Just set up your spinbutton with min value 0 and max value 100. Link the spin button to cell D1. Then in cell A1, enter the formula “=D1/100” and format as a percent. Now you have the same thing without having VBA in your workbook. Personally I avoid it if possible because now with VBA in it, you get the security prompt every time you open the workbook.
Also, your procedures hard-code the linked cell value (C4) in them. If you change the linked cell, you have to remember to change the references in the procedures. Wouldn’t it be better to use the LinkedCell property instead?
FYI your blog is a bit hard to navigate. I click on the post title in Google Reader and it takes me to a home page instead of the article. The article is linked there, but clicking on the article name just reloads the page. I have to click on “cM Blog” in the navbar and then scroll to find the article.