Linking Chart Scale to Cell Value

[JEscobed]

Hello,
I'm trying to link my chart's scale to parameters set in cells E2:E4, using the code shown below. I've seen variations of this code online and people say it works for them, but my chart doesn't scale to the values in E2:E4. Does anyone have any ideas??

Jill

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).MaximumScale = [E2]
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).MinimumScale = [E3]
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).MajorUnit = [E4]

End Sub

 

[GlennUK]

Describe your chart, what type it is, what both axis are, and what they are showing as default, and tell what the contents of E2 E3 and E4 are.

 

[JEscobed]

Hi,
It's a standard line scatterplot, data is below. For chart axis settings, all are showing 'auto' scale. I've clicked 'Microsoft Graph 14.0 Object Library' under references, but I'm not sure if I need it.

Cells A2:C18
1 578.587531 650.1353659
2 576.1179764 649.592439
3 573.8537116 649.0495122
4 571.756588 648.5065854
5 569.7993049 647.9636585
6 564.5851207 646.334878
7 563.0242116 645.7919512
8 561.5366836 645.2490244
9 560.1157442 644.7060976
10 554.9950728 642.5343902
11 553.8358309 641.9914634
12 552.71872 641.4485366
13 551.6412492 640.9056098
14 550.601199 640.3626829
15 547.6866618 638.7339024
16 546.7782782 638.1909756
17 545.8991225 637.6480488

Cells D1:F4

Ax X Y
MaX 10 700
Min -1 300
Tick 1 1

 

[Jon Peltier]

1. You don't need Microsoft Graph; it's completely different.

2. I'm sure you want Chart.Axes(xlCategory) to depend on column E and Chart.Axes(xlValue) to depend on column F.

3. If F2 is 700 and F3 is 300, you don't want F4 to be 1. Try 50 or 100.

4. Use ActiveSheet.Range("E2").Value, not [E2]. The square bracket shortcut notation seems nice and easy now, but it will bite you later.

Here's a more complete version of the code you need:

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet.ChartObjects("Chart 1").Chart ' Category (X) Axis
    With .Axes(xlCategory)
      If ActiveSheet.Range("$E$2").Value > .MinimumScale Then
        .MaximumScale = ActiveSheet.Range("$E$2").Value
        .MinimumScale = ActiveSheet.Range("$E$3").Value
      Else
        .MinimumScale = ActiveSheet.Range("$E$3").Value
        .MaximumScale = ActiveSheet.Range("$E$2").Value
      End If
      .MajorUnit = ActiveSheet.Range("$E$4").Value
    End With ' Value (Y) Axis

    With .Axes(xlValue)
      If ActiveSheet.Range("$F$2").Value > .MinimumScale Then
        .MaximumScale = ActiveSheet.Range("$F$2").Value
        .MinimumScale = ActiveSheet.Range("$F$3").Value
      Else
        .MinimumScale = ActiveSheet.Range("$F$3").Value
        .MaximumScale = ActiveSheet.Range("$F$2").Value
      End If
      .MajorUnit = ActiveSheet.Range("$F$4").Value
    End With
  End With
End Sub

The If/Else/EndIf checks in case the axis scale parameter you want to set is beyond the opposite end of the current axis scale, for example, if the value in E3 (the proposed X axis minimum) is greater than the current X axis maximum.

Another thing: If the values change because of a calculation and not because a different value is entered into a cell, the Worksheet_Change procedure does not run. You need to use something else, like

Private Sub Worksheet_Calculate()

I wrote this up a while back on my web site: Link Chart Axis Scale Parameters to Values in Cells

 

[JEscobed]

Hi Jon,
Actually the code works perfectly for changing values after I followed your instructions on your website (thank you!!!), but you are right that I want it to be based off a calculation. However, when I used:

Private Sub Worksheet_Calculate(ByVal Target As Range)

I get the error 'Procedure declaration does not match description of event or procedure having the same name'. Did I type something incorrectly?

 

[JEscobed]

I apologize, I mis-copied the code. Works perfectly now. Thank you so much!!!!!