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).Chart
        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 > 0 And dTemp <> cht.Axes(xlValue).MajorUnit Then
          cht.Axes(xlValue).MajorUnit = dTemp
        End If    
      Next
    End Sub
  80. There is somthing essential about VBA that eludes me and it’s reflected in Doug’s code. I tried something similar and the fix for him did not work for me.

    The following works for changing one chart at a time:

    ActiveChart.Axes(xlCategory).MaximumScale = [EndDate]   'Set X axis to value in named range EndDate

    The following doesn’t work, although I have used it to change the title and other properties of all charts within the workbook:

    For Each sht In ActiveWorkbook.Worksheets
        For Each cht In sht.ChartObjects
            cht.Activate
            'Do something with the chart...
            With ActiveChart.Axes(xlCategory).Chart.MaximumScale = [EndDate]  'Set X axis to value in named range EndDate
            End With
    
        Next cht
    Next sht

    I’ve tried: adding .Chart as you did with Doug’s code, “With” statements, varaible substitution. I changed xlCategory to xlValue and it did change the y axis. It just doesn’t seem to like the xlCategory. The charts are scatter plots with straight lines, so it may be something there. Besides help on this directly, is there a way to think about VBA code so stuff like addiing “.Chart” becomes more obvious in needed code. 90% of my problems stem from stupid niggly stuff like that! Hell, even the .Axis property needed to magically turn into .Axes for some mysterious reason. Maybe OOP is beyond me.

  81. With/End With indicates an object in the With statement, then between With and End With you do something to the object you’ve indicated, like this:

    With ActiveChart
        .Axes(xlCategory).MaximumScale = [EndDate]
    End With

    But if you only have one statement in the With/Wnd With block, you don’t gain anything by using it. Just this:

    ActiveChart.Axes(xlCategory).MaximumScale = [EndDate]

    But you can make the code better, by not activating the chart. Which will probably save the error you may get by trying to activate the chart on a non-active sheet. Though I just tested this in Excel 2013 and it surprised me by working.

    But this is what I mean:

    For Each sht In ActiveWorkbook.Worksheets
        For Each cht In sht.ChartObjects
            'Do something with the chart...
            cht.Chart.Axes(xlCategory).MaximumScale = [EndDate]  'Set X axis to value in named range EndDate
        Next cht
    Next sht

    And if you are saying [EndDate] to mean the VBA shortcut that evaluates the expression within the brackets, let me advise against it. It’s a great shortcut if you’re typing one-off instructions in the Immediate Window, but in statements within procedures in a big project, it will eventually bite you in the ass with its sharp teeth. Use full referencing of the expression:

    Worksheets("Sheet Name").Range("Name or Address").Value
  82. Several iterations of trying stuff left some cruft, such as the with statement, since I was setting up to do several things with the script. The ActiveChart was carry over from the single chart update. [EndDate] is indeed a named reference, which will get changed, but it worked in the single line and I was trying to eliminate possiblities with the error.

    With:

    cht.Chart.Axes(xlCategory).Chart.MaximumScale = [EndDate]

    I still get “Object doesn’t support this property or meathod.” using Excel 2010. For sanity, I went back and changed the script to update something other than the x axis, which worked.

    For Each sht In ActiveWorkbook.Worksheets
        For Each cht In sht.ChartObjects
            'Do something with the chart...
            cht.Chart.SetElement (msoElementLegendTop)
            'cht.Chart.Axes(xlCategory).Chart.MaximumScale = [EndDate]  'Set X axis to value in named range EndDate
        Next cht
    Next sht
  83. Ack! Another problem crept into my code because I was too lazy to type it all out. Instead I copied yours, which had an extra Chart in it. Unfortunately I didn’t notice.

    Your original

    With ActiveChart.Axes(xlCategory).Chart.MaximumScale = [EndDate]
    End With

    should not have been this

    ActiveChart.Axes(xlCategory).Chart.MaximumScale = [EndDate]

    but this

    ActiveChart.Axes(xlCategory).MaximumScale = [EndDate]

    You should also make sure of certain things when rescaling an axis.
    First, is it a scalable axis, that is a Y or Value axis, or an X axis in a scatter chart, or a Date option X axis in another type of chart.
    Second, you can’t set the axis maximum to a value that’s less than the minimum, if the minimum is not automatic. Nor can you set the axis minimum to a value that’s greater than the maximum, if the maximum is not automatic.

  84. Yeah, I figured there were one to many .Chart in there, but I figured I’d keep poking. I also wondered why changing the legend in this script worked, and why selecting one at a time and updating worked…..

    I had other charts that were not xy charts. The script failed on them before getting to the ones that were active. I had setup some preliminaries and had forgotten about them. Now for an error catch in case I need a mix of charts.

    Thanks Jon – although my first comments, you’ve saved my bacon for years!

  85. Is it possible to retreive the automatically adjusted MaximumScale from one chart and use it as a fixed value in another chart? I want to make 2 charts comparable by matching the y-axis scales.

    I am quite unfamiliar with VBA, so I do not know how to link these values. I tried to retreive the value by:

    Dim MaxYCrt6 As Long
    MaxYCrt6 = ActiveSheet.ChartObject("Chart6").Axes(xlValue).MaximumScale.Value

    And refer to ‘MaxYCrt6’ by using the methods described above. However it does not recognize this value.

    Thanks in advance

  86. Solved it already:

    Private Sub Worksheet_Activate()
    
        ActiveSheet.ChartObjects("Chart 6").Activate
            With ActiveChart.Axes(xlValue, xlPrimary)
        Dim MaxYCrt6 As Long
            MaxYCrt6 = .MaximumScale
        End With
         
        ActiveSheet.ChartObjects("Chart 5").Activate
        ActiveChart.Axes(xlValue).Select
        ActiveChart.Axes(xlValue).MaximumScale = MaxYCrt6
    
    End Sub
  87. Mark –

    Or even this, which avoids activating the charts during the process:

    Private Sub Worksheet_Activate()
         
        ActiveSheet.ChartObjects("Chart 5").Chart.Axes(xlValue).MaximumScale = _
            ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue).MaximumScale
    
    End Sub
  88. That is even easier indeed. I tried a direct link in a number of ways, but could not get it to work. This works perfectly.

    Thanks, Jon!

  89. Hi Jon,

    Thanks very much for your long post. I attempted to use the code you mentioned, but I’m getting the following error:

    “Run-time error ‘2147467259 (80004005)’:

    Automation error
    Unspecified error”

    I’m using Excel 2013 with the latest updates on a fully patched Windows 7 x64 computer. In my workbook I’m using a single worksheet with a single line chart named “Timeline”. Simple code as follows generates the error:

    Print ActiveSheet.ChartObjects(“Timeline”).Chart.Axes(xlCategory).MaximumScale
    or
    ActiveSheet.ChartObjects(“Timeline”).Chart.Axes(xlCategory).MinimumScale = 42300

    I’ve done a ton of searching on the web. Microsoft reports that this error is due to worksheet protection being enabled; that is not true in my case. Since this is an “unspecified” runtime error, I suspect that I’m missing a VBA reference. Currently I’ve got the following references enabled:

    Visual Basic for Applications
    Microsoft Excel 15.0 Object Library
    OLE Automation
    Microsoft Office 15.0 Object Library
    Microsoft Forms 2.0 Object Library

    I also tried enabling Microsoft Graph 15.0 Object Library with no effect. Same with Microsoft ActiveX Data Objects 2.8 Library.

    Any ideas? Any light you can shed will be extremely appreciated!

  90. Hi Aaron –

    The references you listed first are sufficient, none need to be added.

    You need to say Debug.Print, not just Print.

    If the category axis is not a value axis (as in an XY chart) or a date axis (which allows the user to adjust first and last dates), you cannot use this approach to adjust the axis scales, because they are hard-wired to the extent of the data.

  91. Changed “Print” to “Debug.Print”. No effect.

    The category on the X axis (the one I am trying to manipulate) uses a serial number corresponding to a date (for example, 43200). I can change the Minimum and Maximum values manually from the Worksheet by clicking on the chart (plot area) and choosing Format Plot Area > Plot Area Options > Horizontal (Value) Axis – Axis Options [icon] > Axis Options [expand] > Bounds > Minimum (or Maximum). So these items appear to be date values, are not disabled, and therefore should be available programmatically as far I can tell.

  92. Aaron –

    What if you reproduce the chart from scratch in a new workbook. Can you get the code to work then?

  93. I have tried the worksheet change option to create a common axis based on min/max values across three pivot charts. I haven’t had any luck with it actually working. Suggestions?

  94. Forgot to add that I’m using Excel 10.

  95. Robin –

    Worksheet_Change is when a user enters something different into a worksheet cell.

    Use Worksheet_PivotTableUpdate instead.

  96. Hi, Jon,
    Thanks for the good stuff here. Although I read through a lot of comments, I didn’t see an issue where someone has 1 chart on multiple sheets (“Chart 1” on Sheet (1), Sheet (2), Sheet (3), etc. etc.). I used the Worksheet_Calculate to auto scale my Y axis on candle stick graphs. Two questions:

    1. Why do I get Run Time Error -2147024809 (80070057) when I do a Full Calculate (Ctrl + Alt + F9)? I have to press end for each of the worksheets where I have used the Worksheet_Calculate code. This only happens when I do the Full calculate on a sheet without a chart and doesn’t happen when I run a Full Calculate on a sheet with a chart. Everything still works, but pressing end for 100+ sheets is annoying.
    2. Is there any way to run the code on each worksheet by running the Full Calculate? After I run my Full Calculate to get the data to each sheet, I then have to individually calculate each sheet to get the graphs to auto scale.

    Again, thank you very much.
    John

  97. John –

    You have to be very careful about fully qualifying your references. ActiveChart means nothing if the chart is on another sheet. ActiveSheet might not refer to the sheet with the axis scale calculations. For example, the ScaleAxes sub needs to look like this:

    Sub ScaleAxes()
      Dim ws As Worksheet
      Set ws = Worksheets("Sheet1")
      With ws.ChartObjects("Chart 1").Chart
        With .Axes(xlCategory, xlPrimary)
          .MaximumScale = ws.Range("B14").Value
          .MinimumScale = ws.Range("B15").Value
          .MajorUnit = ws.Range("B16").Value
        End With
        With .Axes(xlValue, xlPrimary)
          .MaximumScale = ws.Range("C14").Value
          .MinimumScale = ws.Range("C15").Value
          .MajorUnit = ws.Range("C16").Value
        End With
      End With
    End Sub

    The Worksheet_Change sub like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
      With Worksheets("Sheet1").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
  98. FANTASTIC! Thank you, Jon.

    John

  99. Jon/All –

    This has been a very informative post. The scenario is so very close to what I need, but just not quite. Because I am creating a dashboard, for cleanliness I’ve separated all of my data into a different worksheet. The min, max, and tick cells are located on a separate worksheet entitled “hidden_target_cell”. The charts on worksheet “Dashboard” are dynamically linked to a drop down list on the Dashboard tab.

    I need the chart axes on worksheet “dashboard” to resize using a worksheet change event tied to “hidden_target_cell”.

    Naming conventions used:

    chart_case_accuracy
    Yaxis:
    rng_case_accuracy_max
    rng_case_accuracy_min
    rng_case_accuracy_tick
    Xaxis:
    rng_end_date_max
    rng_start_date_min
    rng_date_tick

    I’d like to replicate the same code 6 more times and just change the named ranges to reflect the different charts. Is this possible?

    Thanks for any insight.

  100. Jack –

    This just becomes an exercise in accounting. For each chart, you’ll have named cells; for chart 1, for example, you’ll have

    rng_case_accuracy_max_1
    rng_case_accuracy_min_1
    rng_case_accuracy_tick_1
    rng_end_date_max_1
    rng_start_date_min_1
    rng_date_tick_1

    For chart 2, the names will end in _2, etc.

    Your code will have a set of commands for each chart, depending on which of the respective names on the other sheet has been changed.

  101. I am a VBA novice and just starting to implement this approach to update existing xy scatter plots with date on x-axis (as explicitly defined in my charts axis number option) . I am primarily interested in updating the x-axis max to an end-date user defined value and derive the minimum with “end-date – 90 days” for all graphs over about ten worksheets (2 graphs per worksheets). Note that the minor and major tick values could be left untouched once set to a fixed value for now and I have commented them out.

    It must be a rookie mistake but I’m stumbling from the initial attempt to update the axis range by running the first code for a selected graph (Alt-F8, i.e. not yet using change event), it displays from 1/1/1900 until 3/15/2023 instead of the desired 90 days date range from October through 12/30/2015. I have tried to define the values as date or number not to avail. I am sieving through this long thread but I am wondering if there is an x-axis property that needs to be added to the code quoted in this tutorial and to watch-out for when handling date x-axis?
    Sorry in advance if I missed a previous answer to this issue… again trying to catch-up over this discussion thread while getting acquainted with VBA in Excel 2010.
    Looking forward to implement event-change or event-calculation to automate my graph x-axis scaling.

    Thank you.

  102. Hi Jon

    I’m finding your stuff really useful, but everything contained on this page is the limit of my vba understanding … suffice to say I’m stumped almost as soon as an error crops up. This one has been particularly vexing for me when I’m trying to update a chart (using the same code as I used for other charts which have worked well…): “Run-time error ‘424’: Object required”. This is the code I’ve been using (adjusted slightly where I’ve named cells, etc.):

    Option Explicit

    Sub ScaleAxes_RISK_TE()
    With ActiveChart.Axes(xlCategory, xlPrimary)
    .MaximumScale = [MAX_X_RISK_TE1].Value
    .MinimumScale = [MIN_X_RISK_TE1].Value
    .MajorUnit = [TICK_X_RISK_TE1].Value
    End With
    With ActiveChart.Axes(xlValue, xlPrimary)
    .MaximumScale = [MAX_Y_RISK_TE1].Value
    .MinimumScale = [MIN_Y_RISK_TE1].Value
    .MajorUnit = [TICK_Y_RISK_TE1].Value
    End With
    End Sub

    Any advice for a novice would be very much appreciated! (Sorry it’s probably a very silly question!).

  103. Hi Jon,

    Many thanks for your post which I’ve found really helpful. I have four graphs on one worksheet and would like the min and max values of the y axis for each graph to be linked to different cells. I read through the discussion thread and saw Dan on 30 December 14 had a similar issue but I couldn’t find the response to this. Unfortunately I am a VBA novice and can’t figure out how to do this – please can you help me?

    Mitesh

  104. Is there an active chart when the code runs? If it runs because of a Worksheet_Change event procedure, there is no active chart because a cell is selected.

  105. Mitesh –

    That old post asked how not to change the X axis, but only the Y axis. The answer is basically to omit the code that changes the X axis (the xlCategory axis).

    Your question seems to be how to apply different Y axis limits to different charts, which is what Jack and I discussed in our comments of 22 and 28 December 2015.

  106. Hi Jon, I should have added that I am using PI Datalink in those worksheet. Again the updated X axis with beginning and end dates are not the one stored in the source cells (B14-16) located on a separate parameter worksheet and the data and graphs are on different worksheets. This is a great tutorial and I hope to make great use of those sample codes to adapt my need.

    Thank you,
    Donat

  107. Hi VBA noob here

    Sub ScaleAxes()
      With ActiveChart.Axes(xlCategory, xlPrimary)
        .MaximumScale = ActiveSheet.Range("GC916").Value
        .MinimumScale = ActiveSheet.Range("GC915").Value
        .MajorUnit = ActiveSheet.Range("GC914").Value
      End With
    End Sub

    This works well if I select one chart and use the macro. However, if I select multiple charts it’ll return an error, how do I modify this VBA to take into account multiple charts?

    Thanks in advance

  108. James –

    You need two routines. One is input a chart, and rescales that chart’s axis:

    Sub ScaleXAxis(cht As Chart)
      With cht.Axes(xlCategory, xlPrimary)
        .MaximumScale = ActiveSheet.Range("GC916").Value
        .MinimumScale = ActiveSheet.Range("GC915").Value
        .MajorUnit = ActiveSheet.Range("GC914").Value
      End With
    End Sub

    The other decides what charts are selected, either an active chart, or several charts:

    Sub ScaleAxes()
      Dim obj As Object
      If Not ActiveChart Is Nothing Then
        ScaleXAxis ActiveChart
      ElseIf TypeName(Selection) = "DrawingObjects" Then
        For Each obj In Selection.ShapeRange
          If obj.HasChart Then
            ScaleXAxis obj.Chart
          End If
        Next
      End If
    End Sub
  109. Hi Jon,
    If I had the knowledge that you and many other here appear to have I think I might rule the world… untill then I, like many others have my own unique situation involving multiple charts… and of course the Unique piece: THe gridlines should never be more than 4 to a chart…. from what I read here (and I read it all) this code you helped doug with would be the best solution… where should I place and what would the statments looklike regarding the gridlines etc… Also any assistance you could provide regarding data labels being applied or deleted dependent upon value would be VERY helpful for my mammoth sized task…. Each of my publications has 8 graphs and I publish anywhere from 5 to ten a month in a VERY tight Timeline. Note I have a few graphs that are dual axis and I have ghost series’ in some that only selected lables should show or should show in specific placement… I can share with you if you wish via emails.

    Dougs Fix:

    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
  110. Worksheet_Calculate goes in the code module corresponding to the chart with the data and chart. “Me” in this module refers to the worksheet. The easiest way to get there is to right click on the sheet tab, and click View Code.

    If your chart has major gridlines, they are automatically positioned on the major tick marks, which are controlled by the MajorUnit. Your best bet is to add them manually and forget them, and just let the major unit you set control the gridlines.

    Sounds like you have a major project on your hands. I and other bloggers are happy to help with parts of a project (like we’re doing in this thread). But for a big project, you probably need to find someone who can help on a professional basis.

  111. I’m linking the Major unit to a value being derived from a formula; the major unit is being updated running the Macro but the values getting displayed on the Major units show more than 2 digits after the decimal point. Is there some way by which I can restrict the the major unit values to only 2 digits after the decimal point.

  112. Thank you so much for taking the time to review and wiegh in on my tasks, I contiued my research and completely aggree regarding the gridelines and Wil follow back with additional solutions as I find them… This is as you suggest, only part of the larger task I am attempting to workthrough and has provided a wealth of learning and experience with little known functions and features… your blogs have been extermely helpful in understanding or walkinging my self through the viable solutions and apply them thoughtfully…

  113. You could round the result of your calculated major unit to two digits. Alternatively you could apply a two-digit number format to the axis.

  114. getting a runtime error 91 in the code. not sure why it fails since it’ pretty much the same as the example minus the option explicit (which by the way doesn’t help). The cells I use have formulas to set the desired axis scales. Interestingly, it has worked a few times but most of the time it doesn’t I’m not sure what changed in those instances. Help anyone?

    Sub ChartAdjustAxes()
    
      With ActiveChart.Axes(xlCategory, xlPrimary)   '< ---- error here
        .MaximumScale = ActiveSheet.Range("D101").Value
        .MinimumScale = ActiveSheet.Range("C101").Value
        .MajorUnit = ActiveSheet.Range("B16").Value
      End With
      With ActiveChart.Axes(xlValue, xlPrimary)
        .MaximumScale = ActiveSheet.Range("D105").Value
        .MinimumScale = ActiveSheet.Range("C105").Value
        .MajorUnit = ActiveSheet.Range("C16").Value
      End With
    End Sub
  115. Option Explicit doesn’t prevent run time errors, it prevents the kind of problem you get if you don’t declare a variable and then later refer to a variable with a subtly different spelling.

    The error message is “Object variable or with block variable not set”, which indicates that there may not be an active chart.

  116. @ Jon Peltier
    I have made sure to select a chart. I get some odd behavior in when the macro chooses to run. It pretty much never runs properly from Excel but sometimes runs from VBE when pressing F5. I have tried isolating the problem by running different combinations of selecting the chart (the whole thing, just the interior, the axes, etc) and from Excel vs VBE, but I can’t seem to figure out when it works and when it doesn’t. I will add that I have this code grouped into a module with all my other chart processing codes – would that make a difference?

  117. I am trying to add a macro for the secondary Y axis on a stock chart. The primary adjusts just fine, but the secondary won’t move when I change the value in the cell. I am just guessing I am coding this wrong? Any help would be much appreciated. Thanks!

    Here is what I have:

    Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet.ChartObjects(“Chart 5”).Chart
    Select Case Target.Address
    Case “$AO$8”
    .Axes(xlValue, xlPrimary).MaximumScale = Target.Value
    Case “$AO$9”
    .Axes(xlValue, xlPrimary).MinimumScale = Target.Value
    Case “$AO$10”
    .Axes(xlValue, xlPrimary).MajorUnit = Target.Value
    Case “$AP$8”
    .Axes(xlValue, xlSecondary).MaximumScale = Target.Value
    Case “$AP$9”
    .Axes(xlValue, xlSecondary).MinimumScale = Target.Value
    Case “$AP$10”
    .Axes(xlValue, xlSecondary).MajorUnit = Target.Value
    End Select
    End With
    End Sub

  118. The code looks okay…

  119. Hmm, can you think of any way to make it work? Basically I have a candlestick chart with moving averages on the secondary axis. I would like the axis to match each other and use a VBA code to automatically update, though if I have to run a code manually not the end of the world. Thanks!

  120. Zach –

    So the primary and secondary axes use the same scales? Then delete the secondary axis. The series on the secondary axis will plot itself on the primary axis if it can’t find a secondary axis. This makes the data all scale properly without having to modify two axes.

  121. Thanks Jon. I don’t think I can without messing up the candlestick chart (e.g. high low bars get messed up). It won’t let me change the chart type of the line charts on the secondary series if that makes sense. This is nuanced, so if you have no solution I get it. Just figured I would ask.

    Thanks!

  122. You can keep the moving averages on the secondary axis, even if the secondary axis has been deleted. I wrote about this long ago in Stock Charts in Excel 2007.

  123. Thanks!

  124. I don’t even know where to begin. How can I make my secondary maximum always 1/100 of my primary axis maximum? I use a format control box so the chart would be considered dynamic based on what location/data I click for it to graph.

  125. Hi, I need help to change the normal axis to a function. and on these axis i need to plot two other functions. How can i do this?

  126. Andrea –

    What’s a Format Control Box? How are you using it?

  127. Alok –

    Do you want to use a function to compute axis limits? Put the calculated limits into the cells that the protocol above uses to set the axis scales, in the Worksheet_Calculate event.

    To plot a function, fill a columnar range with the desired X values, then in the next column use formulas to compute the corresponding Y values, and plot these two columns.

  128. Jon-

    Thanks for the reply. I have two functions each having two independent variables. Hence the problem. It is a operations research problem hence after setting up my axis I have to use two another functions to set up my points.

  129. Alok –

    Does my prior comment help with that?

  130. Jon –
    No it doesn’t help much. But thanks for helping me out

  131. Alok –

    Can you write formulas for the X and Y values you want to plot?

  132. Jon- thanks the last comment helps a lot

  133. I am getting an Run-time error “91”, everytime I try to run the macro. When I click debug the second line is highlighted in yellow. The text highlighted is as follows: With ActiveChart.Axes(xlCategory, xlPrimary). I have not made any changes to the text and am attempting to only recreate the graph with the macro from the original file on this web page. I am trying to understand how to do this to apply it to an excel file I have generated.

    It has worked once, but after that the error returned and continued to occur. No changes were made. Thank you for any help.

  134. That error means “Object Variable Not Set” (it’s always helpful to provide the error message as well as the number, since numbers often are used for multiple unrelated errors). For that line of code it means either a chart is not selected, or the chart somehow has no primary X axis.

  135. I am trying to normalize my scale for my chart in excel. Currently, my chart is set to populate based on what is selected in the drop down menu. The problem I am encountering is, my data has such a wide range of numbers from 1K – 30K, and the auto-scaling is not normalizing the data. If I am plotting Earned = $20, and Spent = $18, the auto-scaling will depict the $2 difference as a major outlier, when it really is not.
    Below is an example of the variance in my data –
    PDMIS Earned Spent
    A 114.3 451.1
    B 1,921.1 3,685.7
    C 5,329.0 5,111.6
    D 23,681.0 22,844.2
    E 31,819.4 25,755.9

    Can I use a VBA to set my max and min values for chart scale, based on what is selected in the drop down menu?

  136. Linda –

    I’ll refer you to Calculate Nice Axis Scales in Excel VBA.

  137. Thank you Jon, I will take a look at your recommendation.

  138. I tried to use the below VBA to set my scale, but I am getting an error. In the actual workbook, I have a formula to calculate what I would like the min and max to be for the Y axis. The min and max is in cell D18 and D19.

    Option Explicit

    Sub ChangeChartFormatting()

    Dim cht As Chart

    Set cht = ActiveSheet.ChartObjects(“Chart 3”).Chart
    ‘adjusts y-axis scale’
    cht.Axes(xlValue).MaximumScale = “$D$18”
    cht.Axes(xlValue).MinimumScale = “$D$19”

    End Sub

    Is my VBA written incorrectly?

  139. I have a dual axis, is this the reason my VBA is not working?

  140. Linda –

    Your syntax isn’t quite right. Try this:

    cht.Axes(xlValue).MaximumScale = ActiveSheet.Range("$D$18").Value
    cht.Axes(xlValue).MinimumScale = ActiveSheet.Range("$D$19").Value

    Also, you need to do this separately for primary Y axis:

    cht.Axes(xlValue).MinimumScale ...

    or

    cht.Axes(xlValue, xlPrimary).MinimumScale ...

    and secondary Y axis:

    cht.Axes(xlValue, xlSecondary).MinimumScale ...
  141. hi jon hope you can help me have a problem with my macro of a chart that changes color according to the sales that are generated each advisor daily, the detail is that when you move from the daily target bar turns green and if not pass your daily goal to become red in the same bar of Annex grafiaca example

  142. jhosep –

    That question is not related to this post. Check out these tutorials to get some ideas:

    VBA Conditional Formatting of Charts by Series Name
    VBA Conditional Formatting of Charts by Value
    VBA Conditional Formatting of Charts by Category Label

  143. Jon,

    I am working on a project with a Gantt chart and would like it to work the following way. Do you have any suggestions?

    Summary page (including the Gantt chart and one of the data points that feeds the Gantt chart dates)
    Backup page (all of the supporting dates and calculations)

    I would like to be able to change the date on the front page and have all of the data automatically update and scale on the Gantt that is on the summary page. I just can’t get it to work with what is provided here.

    This is what I have so far. (It only works when I am on the backup sheet, otherwise it gives me an error). I also have to click to have it recalculate.

    Thanks! This is a pretty awesome post!

  144. The summary page has the chart and the cell in which you enter a date? What data has to update when this date changes, other than the axis scale?

  145. Hi Jon,
    I’m completely new to VBA, and am using Office 365 on a Mac.
    a) On each excel tab, I am doing 2 sets of 3 graphs. 1 set is monthly data, 1 set is for weekly data. Th 3 graphs are different time frames in order to observe changes in the monthly/weekly data moving from 1 time frame to another.
    b) I want to fix the primary y-axis for all the 6 graphs to the same value (example -4% to 4%).
    c) There is also a secondary y-axis, which is 0-100 % (also to be fixed).
    d) I will analyse other data in the same manner in the other tabs, but in each tab the y-axis values max and min values will be different (depending on the range of the data).
    e) I followed the instructions here, as well as some of the suggestions given in the comments, to arrive at a VBA, but it doesn’t work, and honestly I have no idea what is wrong.
    Will you be able to guide me to write up a suitable VBA?

    Thank you in advance for your help!!

  146. SL –

    Which of these axis scales have to change in response to changes to the data? It sounds as if most or all of them can be set once manually and left alone.

  147. Hi Jon,
    Only the vertical axis need to change. The issue is that the graphs will be updated periodically, and I wish that it will remain fixed. Also, the maximum and minimum y-axis values across different time frames may sometimes be different, hence I want to fix it so that visually I can also look at the size of the bars to immediately see which one is a higher value.

  148. SI –

    In your case, rather than a VBA option that you might not want always to run, you could use a hidden series in all the charts you want to have a consistent scale, and use as its data a cell that calculates the appropriate value. With this consistent maximum value plotted in the charts, Excel’s autoscaling would give you consistent axis scales. I wrote about this in Consistent Axis Scales Across Multiple Charts.

  149. Hi Jon,

    I’ve managed to use the codes you’ve suggested to update charts within the worksheet, but what if I have a chart in a separate tab I want to update with the same ranges? Your code only refers to ActiveSheet and I don’t know how to make it apply the cells’ values to other sheets or chart tabs.
    Thank you
    Greg

  150. Hi Jon,
    that’s such a simple and brilliant solution!! I should have thought of it!! Thanks!! Will check out also your other link. :)

  151. Hey Jon,

    I am running into an issue where I have entered the below code for multiple min/max for multiple charts:

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

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

    If wks.Range(“$B$17”).Value cht.Axes(xlValue).MaximumScale Then
    cht.Axes(xlValue).MaximumScale = wks.Range(“$B$17”).Value
    End If
    If wks.Range(“$B$18”).Value cht.Axes(xlValue).MinimumScale Then
    cht.Axes(xlValue).MinimumScale = wks.Range(“$B$18”).Value
    End If

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

    If wks.Range(“$B$32”).Value cht.Axes(xlValue).MaximumScale Then
    cht.Axes(xlValue).MaximumScale = wks.Range(“$B$32”).Value
    End If
    If wks.Range(“$B$33”).Value cht.Axes(xlValue).MinimumScale Then
    cht.Axes(xlValue).MinimumScale = wks.Range(“$B$33”).Value
    End If

    Etc.

    This is getting me to the point where if I change variables to calculate a new Max/Min then the chart axis will also adjust correctly. However, I will be changing variables on other sheets that will then adjust my Max/Min, and when I do so I get the following error:

    “Run-time error ‘-2147024809 (80070057)’:

    The item with the specified name wasn’t found”

    The debugger highlights the below line:

    “Set cht = wks.ChartObjects(“Store”).Chart”

    How can I solve for this where I will be able to enter an input on a separate sheet that will adjust my Min/Max without running into this error?

    Example:

    Workbook1 contains Charts with the code. Sheets within this workbook are linked to Workbook2. I enter an input on Workbook2 that then changes the data in Workbook1. This input creates an error. The numbers change correctly, but the code does not run to adjust the axis. Only when I click F2 in a formula cell and press enter does the axis change.

    Chris

  152. The item with the specified name wasn’t found

    This means it can’t find the chart object named “Store” on the active sheet. You either need to change the name of the chart object to that of a chart object that exists on the active sheet, or you need to redefine wks:

    Set wks = ActiveWorkbook.Worksheets("My Sheet")
  153. I can get this macro to run if I’m on the chart’s tab, but I’d like it to run from another sheet. I have multiple objects on multiple sheets, and I cannot figure out how to explicitly state the objects. Excel seems to ignore object names in VBA code. Here’s what I have:

    Sheet1 (Dashboard) – Tab where selections are made. I’d like to have the chart update after a change is made here.
    Sheet12 (ChartData) – Tab where chart data are stored, based on field selected in Dashboard.
    Chart22 (Chart Projection) – A Chart tab.
    ScaleCht_Min – A named range on the ChartData tab, used to set the minimum value for the y axis.

    This code works when placed on the Chart object.

    Sub Worksheet_Calculate()
     With Chart22.Axes(xlValue, xlPrimary)
            .MinimumScale = Range("ScaleCht_Min").Value
          End With
    End Sub
  154. The code should be on the workbook that is being changed. You use Worksheet_Change if the values in the cells are changed by the user entering a different value, or Worksheet_Calculate if the values are changed because the cell formulas compute different results because of changed precedents.

    The code in Sheet1 would be like this:

    Sub Worksheet_Calculate()
        With Chart22.Axes(xlValue, xlPrimary)
            .MinimumScale = Worksheets("ChartData").Range("ScaleCht_Min").Value
        End With
    End Sub
  155. Jon:

    Wow, that was quick. This works as you suspected it would. Thank you.

  156. Peter Lambrineas says:

    Greetings Jon,
    As a complete VBA novice I’ve found your examples to be very useful. Many thanks for your efforts.

    I tried using your example code posted on Mon, Aug 31, 2015 at 5:25pm. Code was copied and pasted, as is, into an Excel 2010 worksheet. Unfortunately, when I try and use it I get a “Type mismatch” – Error (13) on the “Set cht =….” line immediately after the FOR statement.

    Any thoughts why this is not working for me?
    Cheers, Peter

    Cheers, Peter

  157. Peter –
    1. The code should be pasted into the code module that is associated with the worksheet. Right click on the worksheet tab and choose View Code. The code module that appears is the one that should contain this code.
    2. In that loop, the line

    Set cht = Me.ChartObjects("Chart " & iCht)

    looks for embedded charts in the associated sheet named “Chart 1”, “Chart 2”, and “Chart 3”. If embedded charts with these names are not present, the code will fail.

  158. Peter Lambrineas says:

    Greetings Jon,
    Re: your post, Saturday, July 23, 2016 at 11:58 am. Thanks for your comments.

    I have made a small example Excel .xlsm file based on several methods/examples you have provided earlier in this post stream. All charts and data are on Worksheet 1. These earlier examples based on the “Private Sub Worksheet_Change(ByVal Target As Range)” approach all work correctly. However, the “Private Sub Worksheet_Calculate()” example from the post on Mon, Aug 31, 2015 at 5:25pm does not work.

    On further thought (and comparison with other similar statements in other examples here) I believe the that the troublesome line “Set cht = Me.ChartObjects(“Chart ” & iCht)” needs a “,Chart” appended to the end of the line. When I do this the Type mismatch Error (13) I was getting on this line disappears.

    However, the code now fails on the line “cht.Axes(xlValue).MajorUnit = dTemp” on the first pass through the FOR loop. At each IF statement the variable dTemp is always equal to zero, including the first time it is assigned. The generated “Run-time error ‘-2147467259 (80004005)’: Invalid Parameter” is presumably caused by trying to set the MajorUnit (Tick mark spacing) to a zero value.

    I can send you a copy of my file, to assist you with troubleshooting. Please let me know the email address you would like the file sent to.

    Cheers, Peter

  159. Peter –

    You’re right, it should be

    Set cht = Me.ChartObjects(“Chart ” & iCht).Chart

    I’ve changed the original code in the comment. You also need to test for assigning of a non-positive major or minor unit:

    If dTemp > 0 And dTemp <> cht.Axes(xlValue).MajorUnit Then
  160. Peter Lambrineas says:

    Greetings Jon,
    W.r.t. your example code posted on Mon, Aug 31, 2015 at 5:25pm. and the corrections to this made in your post on Monday, July 25, 2016 at 11:58 am, the updated code still does not work correctly.

    To get the code to work correctly need to also change all the “.Offset(, iCht)” elements to “.Offset(0, 0)” Hence, if the following lines are also updated in the following manner the code will then work.
    1) dTemp = rng.Offset(0, 0).Cells(1).Value
    2) dTemp = rng.Offset(0, 0).Cells(2).Value
    3) dTemp = rng.Offset(0, 0).Cells(3).Value

    It’s been interesting working on this code. Thanks again for your very helpful posts.
    Cheers, Peter

  161. Peter –

    .Offset(0, 0) shouldn’t have any effect. It means take the range and offset it zero rows down and zero columns right. What if you remove the Offset and just use .Cells(i).Value?

  162. Peter Lambrineas says:

    Greetings Jon,

    Yes, the code still works correctly after removing the “.Offset( 0, 0)” from each of the 3 statements identified in my previous post.

    I’d also like to ask you if there is an easy way to provide a “Warning message” whenever an attempt is made to set/change the “Tick Spacing” (MajorUnit) to zero or a negative value, without having the “Warning message” activated when no change has occurred in the cell where the “Tick Spacing” value is held even when changes have occurred in the cells holding the values for the Axes Max and Min settings?
    I’ve tried the code below, with the above effect.

        If dTemp_Y > 0 And dTemp_Y <> cht.Axes(xlValue).MajorUnit Then
          cht.Axes(xlValue).MajorUnit = dTemp_Y
        Else
          Msg1 = "Msg1: Attempt to set Y Tick size to a value <0.0 or =0.0, set value IGNORED LoopNo:= " & iCht
          MsgBox Msg1 'warning message
        End If

    Thanks in advance. Cheers, Peter.

  163. Hello,
    I am writing the VBA to change the Y axis on a line chart
    The Max value is updating but the minimum is not. Please help.
    Here is my current vba

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
      With ActiveSheet.ChartObjects("Chart 1").Chart
        Select Case Target.Address
          Case "$T$17"
            .Axes(xlValue).MaximumScale = Target.Value
          Case "$T$18"
            .Axes(xlValue).MinimumScale = Target.Value
          Case "$T$19"
            .Axes(xlValue).MajorUnit = Target.Value
        End Select
      End With
    End Sub
  164. Joe –

    Is cell T18 being changed? If T18 contains a formula whose value changes, but the formula itself is the same, that does not register as a worksheet_change.

  165. Peter –

    Rather than a warning message, which is going to be subject to worksheet change events and the like, I would simply apply conditional formatting to the cell containing the major unit which displays as red bold italic font if the cell’s value is not positive.

  166. So based on the department input in the top of the sheet the values for the y axis populate via index match functions. These values are pulled from a pivot table and then I use rounding formulas for the minimums and maximums for the data.The formula for the maximum and minimum values for the y axis are as follows =round(max(range),-2) and round(min(range),-2). The value in T17 is the maximum calculation for b18 the minimum and b19 the tick.

  167. Joe –

    So the value in the cell changes because a formula recalculates. Check out the examples in this post and in the long discussion thread that use

    Worksheet_Calculate
  168. How would I create code to keep my intervals on the Y Axis to a set number e.g. 8 intervals so my graphs are consistent…I would allow man and min to be automatic

  169. Jason –

    This could be tricky, because the automatic minimum and maximum are calculated based on the major unit (the interval spacing), and the automatic minimum is zero unless the minimum is at least around 5/6 of the maximum. I might try something like this as a first cut:

    Sub ResetToEightIntervals1()
    With ActiveChart.Axes(xlValue)
    .MinimumScaleIsAuto = True
    .MaximumScaleIsAuto = True
    .MajorUnitIsAuto = True
    .MinimumScale = .MinimumScale
    .MaximumScale = .MaximumScale
    .MajorUnit = (.MaximumScale – .MinimumScale) / 8
    End With
    End Sub

    Of course, your scale might have ugly incremental values like this:

    You might be able to adapt the techniques in my post Calculate Nice Axis Scales in Excel VBA. I was going to try, but I don’t really have time.

Trackbacks

  1. […] Link Excel Chart Axis Scale to Values in Cells | Peltier Tech Blog […]

  2. […] Link Excel Chart Axis Scale to Values in Cells | Peltier Tech Blog […]

  3. […] chart boundaries using cell references. Hi. I've been trying to apply http://peltiertech.com/link-excel-ch…lues-in-cells/ to my spreadsheet. But it's not working and I can't puzzle out what the problem is. Long story […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0

 

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