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

  • 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

K

Kully Rai

New Member
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?
 
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: 
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.
 
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: 
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: 
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
 
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.
 

Attachments

  • Example for Chart Axis.xlsm
    465.4 KB · Views: 20
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: 
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
 

Attachments

  • Example for Chart Axis_Button.xlsm
    467.1 KB · Views: 64
Chihiro said:
You'd need to use VBA. See link for detailed explanation.

http://peltiertech.com/link-excel-chart-axis-scale-to-values-in-cells/
Click to expand...
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: 
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
 
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.
 
  • Like
Reactions: Ria
Chihiro said:
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.
Click to expand...
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
 
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.
 
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.
 

Attachments

  • Dynamic max chart axis.xlsx
    17.7 KB · Views: 27
Hello,

I used your macro with success to control the "primary" Y-Axis vertical scale on my chart. Can I also change the "secondary" Y-Axis vertical scale on the same chart by adding a few more lines to the same macro?

>>> use code - tags <<<
Code: 
Sub ChartMinMax()
Dim iChart As Long
Dim nCharts As Long
nCharts = Sheets("Sheet1").ChartObjects.Count

For iChart = 1 To nCharts
    With ActiveSheet.ChartObjects(iChart).Chart
    .Axes(xlValue).MaximumScale = Sheets("Sheet1").Range("L35").Value
    .Axes(xlValue).MinimumScale = Sheets("Sheet1").Range("L34").Value
    End With
Next
End Sub
 
Along the lines of:
Code: 
.Axes(xlValue, xlSecondary).MinimumScale = 0.1
.Axes(xlValue, xlSecondary).MaximumScale = 1.2
 
Hello - That suggestion worked great for scaling the secondary Y-Axis. Do you have any suggestions for setting the gridline line intervals? This attempt did not work.

>>> use code - tags <<<
Code: 
Sub ChartMinMax()
Dim iChart As Long
Dim nCharts As Long
nCharts = Sheets("Sheet1").ChartObjects.Count

For iChart = 1 To nCharts
    With ActiveSheet.ChartObjects(iChart).Chart
    .Axes(xlValue, x1Primary).MaximumScale = Sheets("Sheet1").Range("L35").Value
    .Axes(xlValue, x1Primary).MinimumScale = Sheets("Sheet1").Range("L34").Value
    .Axes(xlValue, x1Primary).MajorUnit = Sheets("Sheet1").Range("L38").Value
    .Axes(xlValue, xlSecondary).MaximumScale = Sheets("Sheet1").Range("L37").Value
    .Axes(xlValue, xlSecondary).MinimumScale = Sheets("Sheet1").Range("L36").Value
    .Axes(xlValue, x1Secondary).MajorUnit = Sheets("Sheet1").Range("L39").Value
    End With
Next
End Sub
 
You must log in or register to reply here.
Back
Top