1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Chart Axis Scale from Cell

Discussion in 'Discuss Data Visualizations and Charting' started by Kully Rai, Aug 28, 2015.

  1. Kully Rai

    Kully Rai New Member

    Messages:
    17
    I have data in my file from which I produce a chart. Depending on the selection the user chooses from the drop down box, the data will change. I have a few charts based of this same data and I want the axis numbers to remain at the same levels.

    In the data, I work out the MAX value I want on my chart axis. How can I link the chart axis 'maximum' field setting to get the value from this cell?
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
  3. Kully Rai

    Kully Rai New Member

    Messages:
    17
    Hello Chihiro. Thank you for your reply and apologies for the late reply as I have been away from work for the last 2 weeks. I have tried to go through this and enter the VBA code but it does not seem to be working for some reason?

    In a tab named 'Summary' I have 6 different charts side by side and I have named each chart, i.e. Chart0-3, Chart3-5, Chart5-10 etc. I am using the code as per the last section within the link you sent to me where I want the Minimum and Maximum values picked up from cells AK3 and AK2 in a tab named 'Pivot'. I don't want to specify the major or minor units - can I do that? So the first bit of code I typed in was:

    Code (vb):

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet.ChartObjects("Chart0-3").Chart
      Select Case Target.Address
      Case "$AK$2"
      .Axes(xlCategory).MaximumScale = Target.Value
      Case "$AK$3"
      .Axes(xlCategory).MinimumScale = Target.Value
      End Select
      End With
    End Sub
     
     
    However, this does not seem to work when I change my selection of data and I am not sure why. Any help would be appreciated.
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    There are few reasons that comes to mind.

    1) Is the code in the sheet that you are making changes on?
    2) Try inserting below code in standard module and run it. See if your code fires after that.
    Code (vb):
    Sub mEvent()
    Application.EnableEvents = True
    End Sub
    3) Is change made via formula/calculation? This code will not fire, unless change is made (calculation isn't considered Worksheet_Change event).

    To test, put a simple MsgBox code at top of your code and see if it fires. If not, you may want to change the code to launch from button etc.

    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "TEST"
    With ActiveSheet.ChartObjects("Chart0-3").Chart
      Select Case Target.Address
      Case "$AK$2"
      .Axes(xlCategory).MaximumScale = Target.Value
      Case "$AK$3"
      .Axes(xlCategory).MinimumScale = Target.Value
      End Select
      End With
    End Sub
    NARAYANK991 likes this.
  5. Kully Rai

    Kully Rai New Member

    Messages:
    17
    Hi Chihiro. Thank you for the update but I am still struggling - mainly because I am not very familiar with VBA! I have attached a portion of the file I am working on which has the 6 charts that I want to have the same set axis. The changes are made via Pivot Slicers in the 'Summary' tab. The calculation for the Min/Max numbers are in a tab called 'Pivot' in cells U2 & U3. I have tried the code in both work worksheets as I'm not sure where the change is registered - changing of pivot slicer selection or change of min/max values. Any additional help would be greatly appreciated. Thank you.

    Attached Files:

    Thomas Kuriakose likes this.
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    See attached.

    I just created Button to fire the macro. See updated code below, which has been assigned to the button in "Summary" sheet.

    FYI - You use .Axes(xlValue) for your value axis

    Code (vb):
    Option Explicit

    Sub ChartMinMax()
    Dim iChart As Long
    Dim nCharts As Long
    nCharts = Sheets("Summary").ChartObjects.Count

    For iChart = 1 To nCharts
        With ActiveSheet.ChartObjects(iChart).Chart
        .Axes(xlValue).MaximumScale = Sheets("Pivot").Range("U2").Value
        .Axes(xlValue).MinimumScale = Sheets("Pivot").Range("U3").Value
        End With
    Next
    End Sub

    Attached Files:

    Thomas Kuriakose and NARAYANK991 like this.
  7. Kully Rai

    Kully Rai New Member

    Messages:
    17
    Thank you so much Chihiro - that works perfectly! :)
  8. Ria

    Ria Member

    Messages:
    99
    Hi Chihiro:

    I am stuck in same kind of problem. I have 3 charts on one sheet and I want to change vertical scale manually (entering values in a cell). It works fine for first 2 charts because first 2 charts using same cell range data. But for 3rd chart, it does not work. 3rd chart uses different data range/cells. I tried to delete top 2 charts from excel sheet and commented out relavent code, keeping 3rd chart and its code but still it does not work. Any idea and help would be appreciated.
    I have this code under active sheet.

    tHANKS,

    Riaz

    Here is my code:
    Code (vb):
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
       
      With ActiveSheet.ChartObjects("Chart 53").Chart    'THIS PART WORKS
       Select Case Target.Address
          Case "$AN$32"                'MIN VALUE
           .Axes(xlValue).MinimumScale = Target.Value
            .Axes(xlValue).CrossesAt = Target.Value      'cross at minimum value
         Case "$AO$32"              'MAX VALUE                  'FOR X-AXIS
           .Axes(xlValue).MaximumScale = Target.Value
          Case "$AO$33"                'Major Unit
           .Axes(xlValue).MajorUnit = Target.Value
          Case "$AN$33"                'Minor Unit
           .Axes(xlValue).MinorUnit = Target.Value
        End Select
      End With
      With ActiveSheet.ChartObjects("Chart 47").Chart    'THIS PART WORKS
       Select Case Target.Address
          Case "$AN$32"                'MIN VALUE
           .Axes(xlValue).MinimumScale = Target.Value
            .Axes(xlValue).CrossesAt = Target.Value      'cross at minimum value
         Case "$AO$32"              'MAX VALUE                  'FOR X-AXIS
           .Axes(xlValue).MaximumScale = Target.Value
          Case "$AO$33"                'Major Unit
           .Axes(xlValue).MajorUnit = Target.Value
          Case "$AN$33"                'Minor Unit
           .Axes(xlValue).MinorUnit = Target.Value
        End Select
      End With
      With ActiveSheet.ChartObjects("Chart 2-1").Chart    'THIS PART DOES NOT WORK
         Select Case Target.Address
          Case "$AJ$37"                'MIN VALUE
           .Axes(xlValue).MinimumScale = Target.Value
            .Axes(xlValue).CrossesAt = Target.Value      'cross at minimum value
         Case "$AK$37"              'MAX VALUE                  'FOR X-AXIS
           .Axes(xlValue).MaximumScale = Target.Value
          Case "$AK$38"                'Major Unit
           .Axes(xlValue).MajorUnit = Target.Value
          Case "$AJ$38"                'Minor Unit
           .Axes(xlValue).MinorUnit = Target.Value
        End Select
      End With
    End Sub
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    Can you upload the worksheet? I don't see anything that immediately pops up as issue on the code. I'll need to see it in action.
    Ria likes this.
  10. Ria

    Ria Member

    Messages:
    99
    Thanks Chihiro for considering my question. I got it fixed, VBA chart scale was not reading correct chart name and was referring to a hidden chart. Now I found hidden chart and deleted. Now it works fine.

    Thanks all you great people on this forum.

    Riaz
  11. Meeneesh

    Meeneesh New Member

    Messages:
    1
    How can I adjust the X and Y axis with the above code mentioned?

    Thanks!!

    Meeneesh
  12. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,004
    Which code?

    If you mean the one I posted. Look in the attached workbook. U2 & U3 in "Pivot" sheet is where Max/Min is calculated. Then that value is used in "Summary" sheet chart, when you click on update Chart Min/Max.
  13. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    791
    Interesting stuff.

    In some cases, depending on the data lay-out and the chart to make the same can be accomplished using a dummy data series you've made invisible. I've made a very basic sample to showcase. The standard chart would be like below.
    upload_2018-9-21_17-6-10.png
    Selecting any of the categories, like CAT 02, in the slicers generates this chart below. Notice the axis scale did not change.
    upload_2018-9-21_17-7-45.png
    Showing the "max" on the chart reveals:
    upload_2018-9-21_17-11-22.png

    Since I use tables, the max is calculated in the Total row in the example. I've plotted this manually on the chart, converted it to a line chart with no markers. Because there is only one data point, it remains invisible on the chart.

    Attached Files:

Share This Page