Link Excel Chart Axis Scale to Values in Cells

Tuesday, May 27, 2014 by Jon Peltier 234 Comments

Tuesday, May 27, 2014 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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/excel/software/autochart/index.html. 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 incidental to the discussion, but we’ll use the following simple data and chart (named “Chart 1”, the default name of the first chart created in a worksheet). Simple data and scatter chart in Excel

Axis Scale Parameters in the Worksheet

You need a place to put the axis scale parameters. In this example, the range B14:C16 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. Simple data and scatter chart with range containing axis scale parameters

Simple data and scatter chart with range containing axis scale parameters

The cells B14:C16 can contain static values which the user can manually change, or they can contain formulas with your favorite axis scaling algorithms. See how to set up axis-scaling formulas in Calculate Nice Axis Scales in Your Excel Worksheet.

Change Chart Axes with 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, as illustrated in this code sample:

    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.

VBA Procedure to Rescale Chart Axes

Press Alt+F11 to open the VB Editor. In the Project Explorer window, find the workbook to which you want to add code. Project Explorer window of the VB Editor

Project Explorer window of the VB Editor

Double click on the module to open it. If there is no module, right click anywhere in the workbook’s project tree, choose Insert > Module. Or use Insert menu > Module. Up will pop a blank code module. Empty code module

If your module does not say Option Explicit at the top, type it in manually. Then go to Tools > Options, and in the Editor tab check the Require Variable Declaration checkbox. This will place Option Explicit at the top of every new module, saving innumerable problems caused by typos. While in the Options dialog, uncheck “Auto Syntax Check”. This will save innumerable warnings about errors you already know about because the editor turns the font of the offending code red. Tools menu > Options in the VB Editor

You can use a simple procedure that changes the axes on demand. The following changes the scales of the active chart’s axes using the values in B14:C16. Select the chart, then run the code.

Sub ScaleAxes()
  With ActiveChart.Axes(xlCategory, xlPrimary)
    .MaximumScale = ActiveSheet.Range("B14").Value
    .MinimumScale = ActiveSheet.Range("B15").Value
    .MajorUnit = ActiveSheet.Range("B16").Value
  End With
  With ActiveChart.Axes(xlValue, xlPrimary)
    .MaximumScale = ActiveSheet.Range("C14").Value
    .MinimumScale = ActiveSheet.Range("C15").Value
    .MajorUnit = ActiveSheet.Range("C16").Value
  End With
End Sub

You can type all this into the code module, or you can copy it and paste it in. Code module with sample code

Select the chart and run the code. You can run the code by pressing Alt+F8 to open the Macros dialog, selecting the procedure in the list of macros, and clicking Run. Macros dialog with ScaleAxes procedure

Or you could assign the code to a button in the worksheet. Here is the chart after running the code. Simple data and scatter chart after rescaling axes

Simple data and scatter chart after rescaling axes

Worksheet_Change Event Procedure to Rescale Chart Axes

A more elegant approach is to change the relevant axis when one of the cells within B14:C16 changes. We can use the Worksheet_Change event to handle this. For an introductory description of events in Microsoft Excel, check out the Events page on Chip Pearson’s web site (http://cpearson.com/excel/Events.aspx). The Worksheet_Change event procedure fires whenever a cell in the worksheet is changed. To open the code module for a worksheet, right click on a worksheet tab and select View Code from the pop up menu. Or double click on the worksheet object in the Project Explorer window. The code module for the worksheet is opened. Now that we’ve set Require Variable Declaration, note that Option Explicit has appeared automatically atop the module. Empty worksheet code module

You can write the entire procedure yourself, but it’s easier and more reliable to let the VB Editor start it for you. Click on the left hand dropdown at the top of this module, and select Worksheet from the list of objects. Worksheet code module - Objects dropdown

Worksheet code module - Objects dropdown

This places a stub for the Workbook_SelectionChange event in the module. Ignore this for now. Click on the right hand dropdown at the top of this module, and select Change from the list of events. Worksheet code module - Events dropdown

You now have a couple event procedure stubs. Worksheet code module with event procedure stubs

Delete the Worksheet_SelectionChange stub, which we will not be needing, and type or paste the Worksheet_Change code into the Worksheet_Change stub. Worksheet code module with Worksheet_Change event procedure

Worksheet code module with Worksheet_Change event procedure

The code is given below, so you can copy it. When the event fires, it starts the procedure, passing in Target, which is the range that has changed. The procedure uses Select Case to determine which cell was changed, then changes the appropriate scale parameter of the appropriate axis.

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet.ChartObjects("Chart 1").Chart
    Select Case Target.Address
      Case "$B$14"
        .Axes(xlCategory).MaximumScale = Target.Value
      Case "$B$15"
        .Axes(xlCategory).MinimumScale = Target.Value
      Case "$B$16"
        .Axes(xlCategory).MajorUnit = Target.Value
      Case "$C$14"
        .Axes(xlValue).MaximumScale = Target.Value
      Case "$C$15"
        .Axes(xlValue).MinimumScale = Target.Value
      Case "$C$16"
        .Axes(xlValue).MajorUnit = Target.Value
    End Select
  End With
End Sub

Update

In a recent post, I have written about a user-defined function (UDF) that uses VBA to control axis scales. I find it easier to apply than the technique in this tutorial because all of the necessary information is included in the UDF. For example, the last procedure in this tutorial (just above) would look like this in two cells (one cell per axis):

=PT_ScaleChartAxis("Sheet1","Chart 1","X","Primary",B14,B15,B16)
=PT_ScaleChartAxis("Sheet1","Chart 1","Y","Primary",C14,C15,C16)

To read about this UDF, please go to Chart UDF to Control Axis Scale

More Axis Scale Articles

Posted: Tuesday, May 27th, 2014 under VBA.
Tags: Axis Scales.
Comments: 234

Microsoft MVP Logo

Comments

Jeff Weir says

Wednesday, May 28, 2014 at 2:32 am

And they all lived happily ever after. Until someone inserted another row/column in the workbook above/to the left of B14. Or until someone thought “I really don’t want those trigger parameters in the range B14:C16 so I’m going to move them to E20:F22.”

At which case you decide it’s safest to name those input ranges, and make a slight tweak to the code


Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet.ChartObjects("Chart 1").Chart
    Select Case Target.Address
      Case Is = [x_Max].Address
        .Axes(xlCategory).MaximumScale = Target.Value
      Case Is = [x_Min].Address
        .Axes(xlCategory).MinimumScale = Target.Value
      Case Is = [x_Tick].Address
        .Axes(xlCategory).MajorUnit = Target.Value
      Case Is = [y_Max].Address
        .Axes(xlValue).MaximumScale = Target.Value
      Case Is = [y_Min].Address
        .Axes(xlValue).MinimumScale = Target.Value
      Case Is = [y_Tick].Address
        .Axes(xlValue).MajorUnit = Target.Value
    End Select
  End With
End Sub

And they all lived happily ever after. Until someone renamed the chart. But let’s save that for the sequel ;-)

Jeff Weir says

Wednesday, May 28, 2014 at 3:08 am

Forgot to say….great post. It’s fun to change those parameters and watch things adjust. Any chance of a download file, to encourage the folks at home to play along?
Michel says

Thursday, May 29, 2014 at 2:05 am

Nice post as always.
I however, in order to minimize vba routines, use automatic scaling by normalizing the values to numbers between 0 and 1. You can then manipulate the axis to show the values you need. and even set tick lines by using error bars.
I have attached a link to a workbook showing my solution.
Maybe an idea for a post after some tweaking. it actually is made by using information from Jon’s blog

https://drive.google.com/file/d/0B-wGs8ZO23q-SnE3SHdCcklfd1k/edit?usp=sharing
Rohit says

Tuesday, June 3, 2014 at 2:49 am

I have been trying to build a table for a rating system in excel.
The following are the points to be awarded based on productivity and quality parameters for various processes
https://docs.google.com/file/d/0BzYdRWk3IduDV2htZGFHQkZxMDg/

Is there a way I can use vlookup or index/match to populate values in the table below based on the point scheme above ?
https://docs.google.com/file/d/0BzYdRWk3IduDemVzZVRmTFhBTjQ/
Arthur says

Friday, June 27, 2014 at 2:50 pm

Hi to all,

This is a great post. Im not quite experienced with VBA codes and was very easy to adapt to my needs. however I got a question:

I replaced the numbers on the cells with a formula to adjust the max and min values, but when the formula changes the new value, it does not update on the chart, unless I double click-enter on each of the cells.

Is there any way to come around this?
many thanks again,

regards,
Jon Peltier says

Saturday, June 28, 2014 at 4:36 pm
Arthur –
```
Private Sub Worksheet_Change(ByVal Target As Range)
```
fires when a cell is changed, but not when one is recalculated. Instead you need to use
```
Private Sub Worksheet_Calculate()
```
Arthur says

Monday, June 30, 2014 at 12:29 pm

Great!, thanks for the heads up Jon!
best regards,
AC
Brian says

Friday, July 25, 2014 at 2:06 pm

Thank you Jon for a simple and timely fix to a problem I was facing. However, I’m now trying to embed the charts into a Word document, and no matter how I try to paste them into the document – linked by either the Excel or the Word themes – the charts refuse to recognize the reconfigured Y axis. The chart in the Word doc pastes in the fixed values from the Excel chart that I copied and pasted, and won’t readjust as the data (and Y axis ranges) change when I update the Excel chart. Any suggestions? Thanks again for great tips!
Brian
Clemens says

Thursday, July 31, 2014 at 10:01 am

Hi Jon,

Thanks for the explainations. Just like Arthur would I like to run the script when there is a new calculation. Here is what I wrote:

Option Explicit

Private Sub Worksheet_Calculate(ByVal Target As Range)
With ActiveSheet.ChartObjects(“Chart 1”).Chart
Select Case Target.Address

Case “$G$2”
.Axes(xlValue).MinimumScale = Target.Value

End Select
End With
End Sub

However, I get the following error message and the script doesn’t seem to work properly:
Compile Error: Procedure declaration does not match description of event or procedure having the same name

Any idea where the mistake could be?

Thanks and best regards,
Clemens

The Chart

Axis Scale Parameters in the Worksheet

Change Chart Axes with VBA

VBA Procedure to Rescale Chart Axes

Worksheet_Change Event Procedure to Rescale Chart Axes

Update

More Axis Scale Articles

Share this:

Reader Interactions

Comments

Trackbacks

Leave a Reply