Peltier Technical Services, Inc.
- Custom Excel Solutions

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel
- Site Index and Search - RSS

Link Chart Axis Scale Parameters to Values in Cells.

Excel offers two ways to scale chart axes. You can let Excel scale the axes automatically; when the charted values change, Excel updates the scales the way it thinks they fit best. Or you can manually adjust the axis scales; when the charted values change, you must manually readjust the scales. Wouldn't it be great to be able to link the axis scale parameters to values or, even better, formulas in the worksheet? This page shows how to use VBA to accomplish this.

There are a few pieces you need to make this technique work. You need a chart, a set of values for the scaling parameters, and some VBA code to change the axis scales. The code can be either linked to a button, or run from a Worksheet_Change event procedure.

The Chart

The actual mechanics of creating this chart are unimportant to the discussion. For this example, you need a simple XY Scatter chart, which has the primary X and Y axes to be changed by the code below.

Preparing the Scaling Parameters in the Worksheet

In this example, the range D1:F4 is used to hold primary X and Y axis scale parameters for the embedded chart object named "Chart 1". This example can be expanded to include secondary axes, or to change other charts as well.

The cells E2:F4 can contain static values which the user can manually change, or they can contain formulas with your favorite axis scaling algorithms.

Changing Chart Axes in VBA

The parts of Excel's charting object model needed here are the .MinimumScale, .MaximumScale, and .MajorUnit properties of the Axis object (the .MinorUnit property could also be controlled, but I usually do not show minor tick marks). These properties can be set equal to constant values, to VBA variables, or to worksheet range or named range values.

    With ActiveChart.Axes(xlValue, xlPrimary)
        .MaximumScale = 6
            ' Constant value
        .MinimumScale = dYmin
            ' VBA variable
        .MajorUnit = ActiveSheet.Range("A1").Value
            ' Worksheet range value
    End With

If you have a Line, Column, or Area chart with a category-type X axis, you can't use the properties shown above. The maximum and minimum values of a category axis cannot be changed, and you can only adjust .TickLabelSpacing and .TickMarkSpacing. If the X axis is a time-scale axis, you can adjust .MaximumScale, .MinimumScale, and .MajorUnit. You should turn on the macro recorder and format an axis manually to make sure you use correct syntax in your procedure. Any chart's Y axis is a value axis, and this code will work as is.

The Worksheet_Change Event

The Worksheet_Change event procedure fires whenever a cell in the worksheet is changed. If you right click on a worksheet tab and select View Code from the pop up menu, you will be directed to the Visual Basic Editor (VBE), and the code module for the worksheet is opened. Select Worksheet from the left hand dropdown, and Change from the right hand dropdown, and the following procedure skeleton appears. This is the Worksheet_Change event procedure, and you insert the relevant code into it. The argument Target within parentheses tells the procedure which cell has been edited.

    Private Sub Worksheet_Change(ByVal Target As Range)

    End Sub

The completed event procedure, shown below, uses Select Case to determine which cell was changed, then changes the appropriate scale parameter of the appropriate axis.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$E$2"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
            .MaximumScale = Target.Value
    Case "$E$3"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
            .MinimumScale = Target.Value
    Case "$E$4"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
            .MajorUnit = Target.Value
    Case "$F$2"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
            .MaximumScale = Target.Value
    Case "$F$3"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
            .MinimumScale = Target.Value
    Case "$F$4"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
            .MajorUnit = Target.Value
    Case Else
End Select
End Sub

Run as a Regular Macro

You can adapt the code under 'Changing Chart Axes in VBA' above to run within a regular macro, which can be triggered from a button or from the Macro submenu of the Tools menu. This macro should not be in the Worksheet module, but in a regular code module; use an existing regular code module, or select Module from the VBE's Insert menu. Here is an example of such a macro:

Option Explicit

Private Sub ChangeAxisScales()
    With ActiveSheet.ChartObjects("Chart 1").Chart

        ' Category (X) Axis
        With .Axes(xlCategory)
            .MaximumScale = ActiveSheet.Range("$E$2").Value
            .MinimumScale = ActiveSheet.Range("$E$3").Value
            .MajorUnit = ActiveSheet.Range("$E$4").Value
        End With

        ' Value (Y) Axis
        With .Axes(xlValue)
            .MaximumScale = ActiveSheet.Range("$F$2").Value
            .MinimumScale = ActiveSheet.Range("$F$3").Value
            .MajorUnit = ActiveSheet.Range("$F$4").Value
        End With

    End With
End Sub

Peltier Technical Services, Inc.
We can customize or automate any example from this site, or build a new one for you. To discuss your project or obtain a quote, please contact me at jonxlmvp@peltiertech.com

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel - Site Index and Search - RSS

Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile