cellMatrix.net

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.

Excel 2010 Chart Improvements - Migrating Excel 4 Macros to VBA

From the Microsoft Excel Product Team Blog . . .

There are a number of properties for chart elements that were previously only accessible through the Excel 4 Macro Language. We have added additional properties to VBA for these items.

Formula Properties - New properties replace the GET.FORMULA() XLM command, providing formula support for missing chart elements. New Formula properties (Formula, FormulaR1C1, FormulaLocal, FormulaR1C1Local) have been added for AxisTitle, ChartTitle, DisplayUnitLabel, and DataLabel objects.

Series/Point Name - The chart object model currently doesn’t provide a way to determine the series name that is given to a series when it is created. This information is available when hovering over a data point with the mouse, or using the SELECTION() function in XLM. The XLM command returns a point name in the format “SmPn” where m is the series number assigned when the chart is created and n is the point number. For example, when selecting a point on a chart, SELECTION() returns "S1P3". A new Point.Name property has been added that Returns a point name in the format “SmPn” where m is the series number assigned when the chart is created and n is the point number.

Position Properties - The XLM Function GET.CHART.ITEM returns the X,Y coordinates of the corners or mid-points of any chart item. This function had been necessary for some chart elements that did not have positional (.Left, .Top, .Width, and .Height) properties. In Excel 2010 we added .Left, .Top, .Width, and .Height properties for any chart elements that did not have them. This included AxisTitle, ChartTitle, DisplayUnitLabel, Point, and DataLabel objects. Additionally we added a Point.PieSliceLocation Method that returns coordinates of multiple points on Pie Slices.

RefEdit Entries

Recently I've been working on creating a ribbon tab for Excel 2007 that interfaces with and controls several of the add-ins that I use for work. The Goal Seek Tool is one of those add-ins. I created this tool to allow the entry of a single-cell range or value when performing the goal-seek function.

In November 2008 I wrote a short post about my Goal Seek tool. In my first version of that tool, I used a RefEdit control to enter a range and a textbox to enter a value. In a comment to that post, Jon Peltier noted that the textbox could be eliminated and the RefEdit control could be programmed to accept either a range or text entry.

After a lot of time searching the internet, I finally found an example of how this functionality could work on the TM Consulting site. It looks like this:

When wanting to enter a range with RefEdit and assuming the RefEdit control is named "RefEdit1", use

Range(RefEdit1) 

When wanting to enter a value with RefEdit, use

Range(RefEdit1).value 

When wanting to validate the entries made into RefEdit, you can use examples like:

To validate that a range contains a formula, use

HasFormula(Range(RefEdit1)) 

To validate that a range contains a number, use

IsNumeric(RefEdit1)

VBA Fireworks Model from AJP Excel Information

Recently I had the opportunity to build Andy Pope's fireworks display into a line chart that I produce each month. Fireworks were requested because 2009 was the best year on record for a particular business unit at work. When I presented the operating results the animation was a big hit.

Once you download the file you'll find that Andy's display utilizes an XY Scatter chart and VBA code with trigonometry functions to produce the fireworks. Because the code is unprotected you can easily load the modules into your own project and then customize the code as you wish. Even if you don't have an interest in using the display, the file is worth downloading just to study how the model was developed. Thanks Andy for making this available.

Turn off Automatic Hyperlinks

Does it annoy you when Excel automatically turns an email or web address into a hyperlink when you want it entered as text? In Excel 2007 you can prevent this from happening by clicking on the Office button and going to Excel Options -> Proofing -> Deselect the option titled "Ignore Internet and file addresses".

The The Spreadsheet Page has a tip titled Removing or Avoiding Automatic Hyperlinks that covers this very topic. The tip provides the following macro which will allow you to instantly turn all of your hyperlinks into text. The macro really helped me as I had a sheet with over 100 links to zap.

Sub ZapHyperlinks() 
    Cells.Hyperlinks.Delete
End Sub

As I'm just making the switch from Excel 2003 to 2007 it made me think . . . what features do Excel users automatically turn on or off when moving to a different version? Understanding that everyone has their own preferences, I'd like to hear about those features that are a "must have" or that you immediately shut off.