Link Excel Chart Axis Scale 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/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

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

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

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

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

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

Peltier Tech Chart Utility

Comments

  1. 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 ;-)

  2. 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?

  3. 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

  4. 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/

  5. 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,

  6. 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()
  7. Great!, thanks for the heads up Jon!
    best regards,
    AC

  8. 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

  9. 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

  10. Worksheet_Change has an argument Target which indicates which range has changed; Worksheet_Calculate has no such argument, since the whole sheet is recalculated. You’ll need something like this:

    Private Sub Worksheet_Calculate()
      Dim cht As Chart
      Dim wks As Worksheet
    
      Set wks = ActiveSheet
      Set cht = wks.ChartObjects("Chart 1").Chart
    
      If wks.Range("$B$14").Value <> cht.Axes(xlCategory).MaximumScale Then
        cht.Axes(xlCategory).MaximumScale = wks.Range("$B$14").Value
      End If
      If wks.Range("$B$15").Value <> cht.Axes(xlCategory).MinimumScale Then
        cht.Axes(xlCategory).MinimumScale = wks.Range("$B$15").Value
      End If
      If wks.Range("$B$16").Value <> cht.Axes(xlCategory).MajorUnit Then
        cht.Axes(xlCategory).MajorUnit = wks.Range("$B$16").Value
      End If
      If wks.Range("$C$14").Value <> cht.Axes(xlValue).MaximumScale Then
        cht.Axes(xlValue).MaximumScale = wks.Range("$C$14").Value
      End If
      If wks.Range("$C$15").Value <> cht.Axes(xlValue).MinimumScale Then
        cht.Axes(xlValue).MinimumScale = wks.Range("$C$15").Value
      End If
      If wks.Range("$C$16").Value <> cht.Axes(xlValue).MajorUnit Then
        cht.Axes(xlValue).MajorUnit = wks.Range("$C$16").Value
      End If
    End Sub
  11. Are any other changes required to use Private Sub Worksheet_Calculate()? I am running into errors with a simple substitution. Thanks.

  12. Nevermind. The above code works. Thanks.

  13. Thanks a lot, that was a great help!

  14. Okay, I have to ask.. How do you know what the charts name is? I doubleclicked on the chart, then clicked on Properties and it doesn’t tell me.

  15. Rob –

    Select the chart, and look in the Name Box (above cell A1).

  16. I can get the codes to work for one chart, but what if I have multiple charts on the same sheet, with different ranges for axes? Chart 1 would have y-axis values in C26:C28, while Chart 2 would have the values in D26:D28. Ideally they would both run when under worksheet_change or worksheet_calculate.

  17. John, Thanks for the posting. I have been looking for this type of solution for a while. Thanks for helping us out.

  18. Hi Jon,,

    What if I want to add a secondary vertical axis on my chart? How do I modify the Worksheet_Calculate() code to achieve that? Thx

  19. Jonathan –

    The same way I’ve used B14:C16 to hold parameters for the primary X and Y axis scales, you could use D14:E16 for the secondary axis scales, and extend the code to update these as well.

  20. Hi,
    I’m a VBA novice. This code:

    Sub ChangeAxisScales()
    Dim objCht As ChartObject
    For Each objCht In ActiveSheet.ChartObjects
    With objCht.Chart
    ‘ Value (Y) Axis
    With .Axes(xlValue)
    .MaximumScale = Sheets(“Inputs”).Range(“D29”).Value
    .MinimumScale = Sheets(“Inputs”).Range(“D30”).Value
    .MajorUnit = Sheets(“Inputs”).Range(“D31”).Value
    End With
    With .Axes(xlCategory)
    .MaximumScale = Sheets(“Inputs”).Range(“C29”).Value
    .MinimumScale = Sheets(“Inputs”).Range(“C30”).Value
    .MajorUnit = Sheets(“Inputs”).Range(“C31”).Value
    End With
    End With
    Next objCht
    End Sub

    Works fairly well except for 1 main thing and 1 minor thing! The main thing is that I want the worksheet where the data is entered (the Chart 1 is on a different worksheet) to automatically update the chart when a different value is entered (instead of running the macro). The minor thing is that when the x-axis maximum is say 5, the axis goes to up 5 but the last number shown along the axis is 4. How can I change what I have now? I’ve tried various things I’ve seen online but to no avail.

    Best,

    J

  21. J –

    The Worksheet_Calculate or _Change event code has to be on the code module attached to the sheet where the data is located, but the code has to reference the changing chart by the name of the sheet that contains the chart.

  22. This is fantastic. I am using your second example of how to update the axis when new data is entered into the cell. I am having trouble replicating the function to accommodate multiple graphs on a page. I am a vba rookie, so I’m sure its simple, but I have yet to figure it out. Can you show how this would be done for multiple graphs?

    Thanks
    Dave

  23. Hi,

    I would like to know how can I record a macro so I could insert a button in order to refresh the limits instead of running the macro pressing alt+f8 and ‘run’.

    Bests,
    Bernardo

  24. Dave –

    Try this:

    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim cht As ChartObject
      For Each cht In ActiveSheet.ChartObjects
        With cht.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
      Next
    End Sub
  25. Bernardo –

    See my tutorial, How to Assign a Macro to a Button or Shape.

  26. I got it Jon. Works perfectly. Thank you so much for posting this. It makes such a huge improvement on this sheet I am working on. Much appreciated.

  27. Hi,

    I need to trigger this code based on changes happening from two drop down boxes (drop down boxes are form controls in excel). My axis values are dependent on selection from two drop down boxes and for some reason is not triggering this code when i select from the drop downs.

    Can this be done. Thanks for all your help

  28. Aakash –

    Even though the dropdown changes what appears in a cell, it doesn’t trigger a change, since the cell is linked to the dropdown. It’s like a formula that changes its output based on changing inputs does not trigger a change, since the underlying formula is unchanged.

    You can make a simple formula that links to the linked cell of the dropdown. When the dropdown changes, this cell will change, and the Calculate event will fire. Use this code to capture the event:

    Private Sub Worksheet_Calculate()
      ' code here to change axis scale
    End Sub
  29. Thanks for the reply. I tried using the code with Calculate instead of Change but I am thrown a compile error stating “procedure declaration does not match description of event or procedure having the same name”

    To further explain
    I have two worksheets – Charts and Data
    In Charts worksheet drop downs give an option to choose a commodity. Based on the choice, Data worksheet calculates the data. This calculated data is shown in a chart which is shown in the chart worksheet.

    My calculations for axis is also on the chart worksheet . And the axis is calculated using max and min functions linked to data worksheet.

    When i change options in Chart worksheet using the dropdown, the axis do no adjustment accordingly.

    Thanks for all your help

  30. Hi Jon,

    Thanks for this very informative post. I have a few questions that I was hoping you could help me with:

    What if we only wanted to customize the y-axis? i.e. the x-axis will just be dates so there’s no need for me to change those and I would prefer that Excel automatically graphs them as they are doing now. Is there a way to set x-axis values to “auto” or, better yet, ignore the x-axis altogether?

    Also, say I have 20 graphs on one page and would like to set it up so that I would have a code that would basically have separate y-axis input options for each graph (i saw in an earlier response you addressed multiple graphs but I think that was with the same set of y inputs). How would I go about doing that? And is there a way to have certain y-axis values set to “auto” and others set to the actual input options? (Again, similar to what I was looking for above).

    I’d really appreciate any help you can offer!

    Best,
    Dan

  31. Hi Jon,

    Your module Worksheet_Calculate really worked like a charm. Thanks. I used it in my dashboard to set scales.

    Now I’m facing some problems to set horizontal scale automaticaly scale for a scatter chart like this one:

    I know it has to do with chart type but I’m struggling to set min and max scale using calculated argument.

    Any Idea? thanks a lot
    Hector

  32. The horizontal axis is the .Axes(xlCategory) axis of an XY Scatter plot, and this is included in the example code. I can’t tell what the axis settings are before changing them, since all that appear in your screenshot are zero and ­±100%.

  33. Hi Jon,

    Thanks for this, it’s great.

    I am having the exact same problem as Aakash – when I use calculate() to make the graphs update automatically (the max/min cells are based on a formula) I get the following error:
    Run-time error ‘424’:
    Object required

    Then when I debug, the following is highlighted yellow:
    Select Case Target.Address

    Please could you help me on this as I feel like I’m so close!!!

    Thanks a million!

  34. Calculate() doesn’t have a Target range.

  35. Thank you – this is excellent and I have managed to get it to work on a simple scatter chart but I have a couple of questions for my use case.

    1. Is there a way to get it to work for stacked bar charts (I am getting errors)?
    2. If I have two charts in the worksheet but I want the same dynamic axis on both: is it fine to just call them both ‘Chart1’ ?

    What I’m actually using it for is a Gantt chart with a dynamic x-axis (dates). In case it is relevant: I’d also like to incorporate some of the features in the comment above (e.g. using ‘Calculate’ rather than ‘Change’ to pull the range from a formula and having two charts in the same worksheet).

    Thanks very much,
    Alex

  36. 1. In a stacked horizontal bar chart, the X axis (xlCategory) is the vertical axis and the Y axis (xlValue) is the horizontal axis. Make sure you’re specifying the axis you want.
    2. Probably the code would just process the first one it found named “Chart1”, and there are plenty of other reasons not to intentionally use the same name for multiple objects. If there are only two charts, you could do

    Dim cho As ChartObject
    For Each cho In ActiveSheet.ChartObjects
      With cho.Chart
        ' process the axis
      End With
    Next
  37. Jon,

    I am using the worksheet calculate, but I want to apply this formula for every chart in het worksheet.
    can you help me?

    Thanks in advance!

  38. Stijn –

    Instead of

    With ActiveSheet.ChartObjects("Chart 1").Chart
        ' blah blah
    End With

    you need to use

    Dim chtob As ChartObject
    For Each chtob In ActiveSheet.ChartObjects
        With chtob.Chart
            ' blah blah
        End With
    Next
  39. Thanks for your reaction jon, I am still facing an issue. with the beginning of the code below I receive a run time error 438

    Private Sub Worksheet_calculate()
    Dim Chtob As ChartObject
    Dim wks As Worksheet
    Set wks = ActiveSheet

    For Each Chtob In ActiveSheet.ChartObjects
    With Chtob.Chart
    If wks.Range(“$B$24”).Value = Chtob.Axes(xlValue).MaximumScale Then
    Chtob.Axes(xlValue).MaximumScale = wks.Range(“$B$24”).Value

    what am I overlooking?

  40. Try it this way:

    For Each Chtob In ActiveSheet.ChartObjects
        With Chtob.Chart
            If wks.Range("$B$24").Value = .Axes(xlValue).MaximumScale Then
                .Axes(xlValue).MaximumScale = wks.Range("$B$24").Value
  41. I’m trying to use the calculate function to have a set of graphs automatically update based on a certain input parameter I have and then the axes will adjust based on some formulas I have to set them accordingly. The actual chart data is being pulled from other worksheets in the same workbook. The code works great on the chart worksheet for both data and axes, but when I return to any of the data input pages where the data being graphed originates from and try to input more data there, I get a run time error. The code I am using is as follows:

    Private Sub WorkSheet_Calculate()
    Dim cht As Chart
    Dim wks As Worksheet

    Set wks = ActiveSheet
    Set cht = wks.ChartObjects(“Chart 10”).Chart
    cht.Axes(xlCategory).MaximumScale = wks.Range(“$b$118”).Value
    cht.Axes(xlCategory).MinimumScale = wks.Range(“$c$118”).Value
    cht.Axes(xlValue).MaximumScale = wks.Range(“$b$119”).Value
    cht.Axes(xlValue).MinimumScale = wks.Range(“$c$119”).Value

    and so on for additional charts……

    Any idea where I am off the rails?

  42. What line is highlighted when you get the error, and what is the error description?

  43. The highlighted line is
    Set cht = wks.ChartObjects(“Chart 10”).Chart

    The error reads:
    Run-time error ‘-2147024809 (80070057)’:
    The item with the specified name wasn’t found.

    I will note that the error only occurs on the other sheets in the workbook where data is entered to continue populating the graphs. Each time anew value is entered, this error pops up, but if I click “end” on the error message, it will accept the entry and graph it properly.

  44. Hello Alll,

    Can some explain the mechanism of stteing up scale in excel, because I’m building a software where data can be plotted on charts like excel, but i could not figure out how to let the software choose scale for data points.

    thanks!

  45. Zeyad –

    You’ll have to find documentation for the other software, if it’s not Excel.

  46. Hey guys,

    I’m a VBA & Macro novice. Can someone help me figure out what I’m doing wrong. This is a line graph linked to cells that change depending on what a drop down box says. I’m using the code Jon said to use when the cells are calculating and changing but I keep getting an error on (If wks.Range(“$U$35”).Value <> cht.Axes(xlCategory).MaximumScale Then).

    This is the code I’m using and I only changed the cells to pull from where I have my data.

    Private Sub Worksheet_Calculate()
      Dim cht As Chart
      Dim wks As Worksheet
    
      Set wks = ActiveSheet
      Set cht = wks.ChartObjects("Chart 2").Chart
    
      If wks.Range("$U$35").Value <> cht.Axes(xlCategory).MaximumScale Then
        cht.Axes(xlCategory).MaximumScale = wks.Range("$U$35").Value
      End If
      If wks.Range("$U$36").Value <> cht.Axes(xlCategory).MinimumScale Then
        cht.Axes(xlCategory).MinimumScale = wks.Range("$U$36").Value
      End If
      If wks.Range("$U$37").Value <> cht.Axes(xlCategory).MajorUnit Then
        cht.Axes(xlCategory).MajorUnit = wks.Range("$U$37").Value
      End If
      If wks.Range("$V$35").Value <> cht.Axes(xlValue).MaximumScale Then
        cht.Axes(xlValue).MaximumScale = wks.Range("$V$35").Value
      End If
      If wks.Range("$V$36").Value <> cht.Axes(xlValue).MinimumScale Then
        cht.Axes(xlValue).MinimumScale = wks.Range("$V$36").Value
      End If
      If wks.Range("$V$37").Value <> cht.Axes(xlValue).MajorUnit Then
        cht.Axes(xlValue).MajorUnit = wks.Range("$V$37").Value
      End If
    End Sub

    Thanks

  47. Great stuff Jon,
    Can’t believe MS hasn’t added an option in to excel for this yet.

    I’m using your calculate() code from the comments but am getting a runtime error ‘-2147467259 (80004005)’ saying that the ‘Maximum Scale’ of object ‘Axis’ failed and it highlights:

    If wks.Range("$U$35").Value <> cht.Axes(xlCategory).MaximumScale Then

    All I did was change the cell to U35 so I must be missing something. Sorry I’m new at this. Thanks!

  48. Josh –

    What is the error?
    Is that the right chart name (“Chart 2”)?
    Is the chart a scatter chart? If not, unless the axis is a date axis, it has no minimum or maximum that you can set.

  49. Jet –

    Is the chart a scatter chart? If not, unless the axis is a date axis, it has no minimum or maximum that you can set.

  50. It’s a line graph. For some reason the auto adjust is always starting at my y-axis at zero so I was trying to use your solution to have my axis reference cells that tell it what to adjust to, and to have equations in those cells that adapt to my changing data. But now I’m seeing some of my other graphs are auto adjusting and they dont start at zero. I’m all confused. Why are some still keeping the minimum auto adjust of the y axis at zero?

  51. Josh –

    The Y axis is the xlValue axis. You only need the steps that include cht.Axes(xlValue), not cht.Axes(xlCategory).

    Depending on the data, Excel may decide not to use zero as the minimum. If the minimum of the data is 5/6 or greater of the maximum, then Excel will use the highest multiple of the major unit which is lower than the minimum.

  52. Thanks for the help Jon, let me show you and maybe that can explain things better… this is the graph I’m working on. As you can see, the auto adjusting y axis doesn’t want to even out the graph and leaves it’s minimum at zero revealing a squished graph.

    Using what you said, and only using the (xlValue) aka y-axis changes, I still keep getting errors like this.

    I must be overlooking something simple?

  53. Josh –

    Is “Plant Comparison” a column chart series? Those #N/A will encourage Excel to think the axis minimum should be zero if it’s a clustered column. If there are no other columns in the chart, change it to a stacked column, and Excel will stop considering those #N/A values as zeros. Then you might get some reasonable autoscale axis limits. Yeah, it’s confusing; I only learned that little gem last night.

    FWIW, your labels in U34 and V34 should be switched.

    I still don’t get that error. Try something like this to see which part of that line of code is giving you that error:

      Dim TempValue As Double
      If wks.Range("$U$35").Value <> cht.Axes(xlValue).MaximumScale Then
        TempValue = wks.Range("$U$35").Value
        cht.Axes(xlValue).MaximumScale = TempValue
      End If
  54. All the data points in the left chart are linked to the graph, but I have IF statements referencing the yellow drop down menu so that if it says something like “Monthly” in the drop down the cells with annual information will show as an NA() so it wont get graphed. In that particular graph, both the MAX Tons and Total Tons data points are graphed. If I stack them the Max Tons doesn’t show as a straight line across the top to show the limit we’re approaching.

    Thanks for the label call out. :)

    So I edited the code a bit to this:

    http://i1054.photobucket.com/albums/s484/foxman2501/VBA%20for%20Chart_zpsptxhbojw.jpg

    And now its not erroring out but my graph turned to this monstrosity:

    http://i1054.photobucket.com/albums/s484/foxman2501/Chart%201_zpsyzvfivo8.jpg

    BUT!!!! I found a work around!!! I let excel choose the max and tick marks itself and ONLY had it pull the minimum from my cells.

    Only using:
    Private Sub Worksheet_Calculate()
    Dim cht As Chart
    Dim wks As Worksheet

    Set wks = ActiveSheet
    Set cht = wks.ChartObjects(“Chart 2”).Chart

    If wks.Range(“$U$36”).Value cht.Axes(xlValue).MinimumScale Then
    cht.Axes(xlValue).MinimumScale = wks.Range(“$U$36”).Value
    End If
    End Sub

    For some reason, excel likes that and we have my new graph looking like so!

    http://i1054.photobucket.com/albums/s484/foxman2501/Chart%201_zpsyoi3zy9m.jpg

    That’s really all I needed! Thanks Jon! You’ve been a huge help!

  55. All I had time to look at was the chart with the gray background. It’s gray because there are too many gridlines: with a major tick spacing of 110 and a max minus min of 310000 minus 200000, there are 1000 gridlines across the chart, close enough to display as a solid color. That calculation needs to be fixed.

    But as you discovered, all you really needed was to set the minimum.

  56. Hi Jon,

    Thanks for the excellent post – it works very well.

    I’m trying to use excel to help create a graphic – its for a communication piece – that shows the number of customers in any of our 30 branches.

    I’d like the scale to show the exact amounts of the customers we started with and the number we’re aiming for, but the numbers get rounded on the chart e.g. using a min y axis value of 1,491 gets translated to 1,359. How can I get the axis to start at 1,491?

    Thanks for your help!

  57. If you enter a specific value for axis minimum, Excel should use that value.

  58. Jon

    Thanx so much for this post/blog…. it has really helped me out a hell of a lot…

    Cheers

    MikeT

  59. Jon,

    While these posts, questions, and your answers have taught me a lot I still am not able to accomplish what I intended. Similar to Aakash and J osh, I have a drop down menu with varying percent growth associated to the “total” column adjacent to the drop down. I can change the drop down percent growth, and the “total” with calculate accordingly, my chart will even update and show the new values just fine. My problem is the axis will not adjust until I double click the “Total” cell and hit enter. I attempted to get rid of this step by doing Worksheet_Calculate() but that did not work. I must not be entering the code on the specific worksheet correctly. I then tried to put a form button as you referenced in another post of yours, however I could not find the Macro I wrote under the “Assign a macro” selection. I would be ok with having to click an “update” button so once the drop down is changed I can hit “update” and the vertical axis will adjust to make a more useful graph. Also, should I be trying to write these changes in the worksheet or the module?

    Data plots and updates just fine, I just cannot figure out how to get the limits to AUTOMATICALLY update after the value changes. I’m sure Calculate() is correct I just cannot determine where I screwed up.

    Here Is my code via “Sheet 2”

    Option Explicit
    Private Sub setLimits(aChart As Chart, MaxVal As Double, _
    MinVal As Double)
    With aChart
    .Axes(xlValue).MaximumScale = MaxVal
    .Axes(xlValue).MinimumScale = MinVal
    End With
    End Sub

    Private Sub Worksheet_Calculate()
    With ActiveSheet
    setLimits .ChartObjects(1).Chart, _
    .Range(“$Q$49”).Value, .Range(“$Q$48”).Value
    End With
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target.Parent
    If Intersect(Target, .Range(“$Q$49,$Q$48”)) Is Nothing Then Exit Sub
    setLimits .ChartObjects(1).Chart, _
    .Range(“$Q$49”).Value, .Range(“$Q$48”).Value
    End With
    End Sub

  60. Hi Jon,

    Thanks for responding. See below. I used the macro to set the max and min based on the values at the top, but the min gets rounded down for some reason

  61. Many thanks for the posts! Has anyone tried running these on a protected worksheet? I’m using the VBA code below and it works great if the sheet is unprotected, however it ceases working if I protect the worksheet. Any thoughts?

    Thanks,

    Bill

    Private Sub Worksheet_Calculate()
      Dim cht As Chart
      Dim wks As Worksheet
      Set wks = ActiveSheet
      Set cht = wks.ChartObjects("Case 1 Frustum").Chart
      If wks.Range("$B$45").Value  cht.Axes(xlCategory).MaximumScale Then
        cht.Axes(xlCategory).MaximumScale = wks.Range("$B$45").Value
      End If
      If wks.Range("$B$45").Value  cht.Axes(xlValue).MaximumScale Then
        cht.Axes(xlValue).MaximumScale = wks.Range("$B$45").Value
      End If
    End Sub
  62. If the chart is locked, then protecting the worksheet prevents manual and programmatic changes to it.

    Your code can unprotect the worksheet, then reprotect it in such a way that subsequent code bypasses the protection:

    Private Sub Worksheet_Calculate()
      Dim cht As Chart
      Dim wks As Worksheet
    
      ActiveSheet.Unprotect Password:="MyPassword"
      ActiveSheet.Protect Password:="MyPassword", UserInterfaceOnly:=True
    
      Set wks = ActiveSheet
      Set cht = wks.ChartObjects("Case 1 Frustum").Chart
      If wks.Range("$B$45").Value  cht.Axes(xlCategory).MaximumScale Then
        cht.Axes(xlCategory).MaximumScale = wks.Range("$B$45").Value
      End If
      If wks.Range("$B$45").Value  cht.Axes(xlValue).MaximumScale Then
        cht.Axes(xlValue).MaximumScale = wks.Range("$B$45").Value
      End If
    End Sub
  63. I’m getting an error, this property is not used by category axes.

    Do you know why? It worked the first time when I followed the first part of this tutorial, until I followed the second part to have it automatically update.

    Thanks!

  64. Albert –

    In order to be able to modify the X axis (Category axis) using this technique, the chart must be an XY chart (in which the X axis uses the same value type configurations as a Y Value axis), or the chart must be a Line or other type chart with its X axis formatted as a Date axis.

  65. Thanks for the quick reply Jon. Would you know of a way to do this for bar graphs? I am simply trying to have the date as the x-axis.

  66. Albert –

    You mean a horizontal bar graph? The X axis (category axis) of a horizontal bar graph is the vertical axis, the Y axis (value axis) is the horizontal axis.

  67. Hi,
    I’m trying to create an easy to use menu engineering worksheet. One of the items I am trying to make is a contribution margin matrix, for which I have already used a scatter plot. But contribution margin matrices have quadrants that are divided up by a horizontal and a vertical line intersecting the plane. These lines have to be equal to the average contribution margin and the approximated sales percent for the matrix to classify items properly and I have already put the formulas for the contribution margin and sales percent into cells. So my question is: Is there a way, using VBA, to set the axis values that the vertical and horizontal axes cross equal to the cells in which I have the contribution margin and sales percent?

  68. Jon,

    Immensely useful. Thanks for the top tip and taking time to write this article.

    Rob

  69. This one ended up working for me:

    Jon Peltier says:
    Thursday, July 31, 2014 at 2:12 pm
    Worksheet_Change has an argument Target which indicates which range has changed; Worksheet_Calculate has no such argument, since the whole sheet is recalculated. You’ll need something like this:

    Private Sub Worksheet_Calculate()
      Dim cht As Chart
      Dim wks As Worksheet
    
      Set wks = ActiveSheet
      Set cht = wks.ChartObjects("Chart 1").Chart
    
      If wks.Range("$B$14").Value <> cht.Axes(xlCategory).MaximumScale Then
        cht.Axes(xlCategory).MaximumScale = wks.Range("$B$14").Value
      End If
      If wks.Range("$B$15").Value <> cht.Axes(xlCategory).MinimumScale Then
        cht.Axes(xlCategory).MinimumScale = wks.Range("$B$15").Value
      End If
      If wks.Range("$B$16").Value <> cht.Axes(xlCategory).MajorUnit Then
        cht.Axes(xlCategory).MajorUnit = wks.Range("$B$16").Value
      End If
      If wks.Range("$C$14").Value <> cht.Axes(xlValue).MaximumScale Then
        cht.Axes(xlValue).MaximumScale = wks.Range("$C$14").Value
      End If
      If wks.Range("$C$15").Value <> cht.Axes(xlValue).MinimumScale Then
        cht.Axes(xlValue).MinimumScale = wks.Range("$C$15").Value
      End If
      If wks.Range("$C$16").Value <> cht.Axes(xlValue).MajorUnit Then
        cht.Axes(xlValue).MajorUnit = wks.Range("$C$16").Value
      End If
    End Sub

    My question is, how would I apply this to two charts? I have three charts on my sheet (Chart 1, Chart 2, Chart 3) and I’d like to apply this to only charts 1 and 2.

    Thanks!!!

  70. Greg –

    You need to put this code inside a loop:

    Private Sub Worksheet_Calculate()
      Dim cht As Chart
      Dim wks As Worksheet
      Dim vChart As Variant
      Dim iChart As Long
    
      vChart = Array("Chart 1", "Chart 2")
    
      Set wks = ActiveSheet
      
      For iChart = LBound(vChart) To UBound(vChart)
        Set cht = wks.ChartObjects(vChart(iChart)).Chart
      
        If wks.Range("$B$14").Value <> cht.Axes(xlCategory).MaximumScale Then
          cht.Axes(xlCategory).MaximumScale = wks.Range("$B$14").Value
        End If
        If wks.Range("$B$15").Value <> cht.Axes(xlCategory).MinimumScale Then
          cht.Axes(xlCategory).MinimumScale = wks.Range("$B$15").Value
        End If
        If wks.Range("$B$16").Value <> cht.Axes(xlCategory).MajorUnit Then
          cht.Axes(xlCategory).MajorUnit = wks.Range("$B$16").Value
        End If
        If wks.Range("$C$14").Value <> cht.Axes(xlValue).MaximumScale Then
          cht.Axes(xlValue).MaximumScale = wks.Range("$C$14").Value
        End If
        If wks.Range("$C$15").Value <> cht.Axes(xlValue).MinimumScale Then
          cht.Axes(xlValue).MinimumScale = wks.Range("$C$15").Value
        End If
        If wks.Range("$C$16").Value <> cht.Axes(xlValue).MajorUnit Then
          cht.Axes(xlValue).MajorUnit = wks.Range("$C$16").Value
        End If
      Next
    End Sub
  71. Jon,

    many thanks for the article.

    Now, I have a lot of charts on the same worksheet, and all use the same cell reference for the min and max values of the y-axis.

    I have a very basic knowledge of VBA and programming, but I guess I have to set up a code that counts the charts in the worksheet and then start a loop.

    I’ve tried the code below, but I get the error “The object doesn’t support this method or property”.

    Sub ScaleCharts()
        Dim iChart As Long
        Dim nCharts As Long
    
        nCharts = ActiveSheet.ChartObjects.Count
    
        For iChart = 1 To nCharts
            With ActiveSheet.ChartObjects(iChart).Axes(xlValue, xlPrimary)
                .MaximumScale = ActiveSheet.Range("B20").Value
                .MinimumScale = ActiveSheet.Range("B19").Value
            End With
        Next
    End Sub
  72. Federico –

    You left out an important keyword:

    With ActiveSheet.ChartObjects(iChart).Chart.Axes(xlValue, xlPrimary)
  73. This is a really helpful post. I was able to get this to work for one chart, but I have several y-axes to update on the same tab. Ideally I would like each chart to link to a different range, but I will settle for all linking to the same range if I can get that to work! Are you able to help me out with where I went wrong? Thanks in advance!

    Private Sub Worksheet_calculate()
      Dim Chtob As ChartObject
      Dim wks As Worksheet
      Set wks = ActiveSheet
    
      For Each Chtob In ActiveSheet.ChartObjects
        
        With Chtob.Chart
          If wks.Range("$B$4").Value <> .Axes(xlValue).MaximumScale Then
            .Axes(xlValue).MaximumScale = wks.Range("$B$4").Value
          End If
          If wks.Range("$B$5").Value <> .Axes(xlValue).MinimumScale Then
            .Axes(xlValue).MinimumScale = wks.Range("$B$5").Value
          End If
          If wks.Range("$B$6").Value <> .Axes(xlValue).MajorUnit Then
            .Axes(xlValue).MajorUnit = wks.Range("$B$6").Value
          End If
        End With
      Next
    End Sub
  74. Doug –

    I’ve modified your code. It should work now.

  75. Thanks, Jon. Really appreciate the quick response and the help. Your edits work wonderfully.

    Is it exponentially more difficult to expand this concept to assign different charts (say 4-5 charts) on the same tab to different cell values?

    I think it’s time for me to find a VBA course and get learning.

  76. Doug –

    Conceptially it’s not harder to do multiple charts from separate ranges. You just need to pay more attention to the bookkeeping

  77. At the risk of overstaying my welcome – can you help me with the syntax?

    I’m using ‘Private Sub Worksheet_calculate()’ to reassign the axes when the cells are refreshed, and would like to be able to assign it along the lines of:

    Chart 1:
    Max: B4
    Min: B5
    Major unit: B6

    Chart 2:
    Max: C4
    Min: C5
    Major Unit: C6

    Chart 3:
    Max: D4
    Min: D5
    Major Unit: D6

    Etc..

    Thanks again, Jon. Really appreciate your help.

  78. In case it’s helpful for anyone else out there, I think I solved my problem using the following approach.

    Private Sub Worksheet_Calculate()
      Dim cht As Chart
     
        Set cht = Me.ChartObjects("Chart 1").Chart
         
          If Me.Range("$b$4").Value <> cht.Axes(xlValue).MaximumScale Then
            cht.Axes(xlValue).MaximumScale = Me.Range("$B$4").Value
          End If
          If Me.Range("$B$5").Value <> cht.Axes(xlValue).MinimumScale Then
            cht.Axes(xlValue).MinimumScale = Me.Range("$B$5").Value
          End If
          If Me.Range("$B$6").Value <> cht.Axes(xlValue).MajorUnit Then
            cht.Axes(xlValue).MajorUnit = Me.Range("$B$6").Value
          End If
           
     Set cht = Me.ChartObjects("Chart 2").Chart
         
          If Me.Range("$C$4").Value <> cht.Axes(xlValue).MaximumScale Then
            cht.Axes(xlValue).MaximumScale = Me.Range("$C$4").Value
          End If
          If Me.Range("$C$5").Value <> cht.Axes(xlValue).MinimumScale Then
            cht.Axes(xlValue).MinimumScale = Me.Range("$C$5").Value
          End If
          If Me.Range("$C$6").Value <> cht.Axes(xlValue).MajorUnit Then
            cht.Axes(xlValue).MajorUnit = Me.Range("$C$6").Value
          End If
    
    'Etc...
    
    End Sub
  79. In fact, if you are using a well-defined range, and the charts are all named properly, you can use a loop.

    Private Sub Worksheet_Calculate()
      Dim iCht As Long
      Dim cht As Chart
      Dim rng As Range
      Dim dTemp As Double
    
      Set rng = Me.Range("A4:A6")
    
      For iCht = 1 To 3 ' to the number of charts
        Set cht = Me.ChartObjects("Chart " & iCht)
        dTemp = rng.Offset(, iCht).Cells(1).Value
        If dTemp <> cht.Axes(xlValue).MaximumScale Then
          cht.Axes(xlValue).MaximumScale = dTemp
        End If
        dTemp = rng.Offset(, iCht).Cells(2).Value
        If dTemp <> cht.Axes(xlValue).MinimumScale Then
          cht.Axes(xlValue).MinimumScale = dTemp
        End If
        dTemp = rng.Offset(, iCht).Cells(3).Value
        If dTemp <> cht.Axes(xlValue).MajorUnit Then
          cht.Axes(xlValue).MajorUnit = dTemp
        End If    
      Next
    End Sub

Speak Your Mind

*

Peltier Tech Chart Utility

 

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