Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Excel Dashboards

 

Books at Amazon.com

 

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.

If you want a ready-to-use solution, try Tushar Mehta's AutoChart Manager add-in, available as a free download at http://www.tushar-mehta.com.

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.

 DEF
1AxesXY
2Max66
3Min00
4Tick11

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

For an introductory description of events in Microsoft Excel, check out the Events page on Chip Pearson's PSC Excel Web Source (http://cpearson.com/excel/events.htm).

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

 

 

Page copy protected against web site content infringement by Copyscape

 

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2013. 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

Microsoft Most Valuable Professional

My MVP Profile