Scaling Charts

[KatieW]

I am trying to get the mininmum value of my Y axis to link to a cell with in
my worksheet. I want the other axis settings to remain on auto. I want the
minimum value to equal sell $S$3 everytime Cell $A$3 changes. Below is what
I have so far, but this doesn't work. When I run the code the End Sub is lit
up.

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address = "$A$3"
Case Range("$S$3")
With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue)
.MinimumScale = "$S$3"
End With
End Sub

 

[B Lynn B]

To be sure your code runs every time A3 changes:

If Not Intersect(Target, Range("A3")) Is Nothing Then
With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue)
.MinimumScale = Range("S3").Value
End With
End If

Also, unless you have a control in place to ensure S3 has a valid numeric
value, you should probably include some form of error handling.

 

[KatieW]

Thank you B Lynn B,

I did realize that I was referencing the wrong chart; below is the new code.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A3")) Is Nothing Then
With ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue)
.MinimumScale = Range("S3").Value
End With
End If

I'm getting the compile error on the "End If" I also tried "End Sub" same
error. Also, the minimum scale value still isn't changing.

Also, thanks for the tip I added some error handling to S3 to ensure a round
and positive value.

 

[B Lynn B]

You need both the End If and the End Sub lines, as in below. That alone may
make your code actually run so the scale value will change on your page.
Otherwise, you could look at a couple of things. You might want to check
that you have only one chart on the page with the name "Chart 2". It is
possible to have duplicate use of the name, which definitely could confuse
things. You may also need to specify the second argument for the Axes
method, which I went ahead and added below.

Beyond that, I know I've heard there are lots of differences between Excel
2007 and earlier versions when it comes to charts. So if you're not running
2007, I suppose that could make the code work for me but not for you.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A3")) Is Nothing Then
With ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue, xlPrimary)
.MinimumScale = Range("S3").Value
End With
End If

End Sub