VBA control chart Y-Axis

[Juriemagic]

Hi good people!,

I have this code:

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet.ChartObjects("Chart 5").Chart
Select Case Target.Address
Case "$A$2"
.Axes(xlValue).MaximumScale = Target.Value
Case "$A$3"
.Axes(xlValue).MinimumScale = Target.Value
Case "$A$4"
.Axes(xlValue).MajorUnit = Target.Value
End Select
End With
End Sub



which I got from, I think Mark Peltier's site, but just cannot get it to work. I have a dropdown which selects the criteria I want to view on the chart. The Chart itself works fine, I just need to have the Y-axis scale according to what I am viewing. Please, if someone could shed some light on this for me?

[Aflatoon]

It's Jon Peltier, not Mark. ;)

Please define "cannot get it to work" - what happens?

[Juriemagic]

Oops, apology for getting that wrong.

Well, Nothing happens, the chart chooses it's own range as I go along. I have a scroll bar which clicks through a date range, and every date range obviously has different values, so every click changes the chart and also the Y-axis values. What I have is in A2 there is an IF formula, which changes the MAX I want, depending on what I'm viewing, In A3 I have a zero (MIN value), and in A4 another IF formula which sorts out the Major unit value. But all this is ignored, so to speak..

[Aflatoon]

Formulas don't trigger the change event. You could simply assign a macro to the scrollbar to update the axis. To use the Change event you'd need to monitor the input cell, not the formula cell, or you could use the sheet's Calculate event instead. You could even trap the calculate event of the chart itself if you want.

[Juriemagic]

I have also tried this now..no joy..Please would you help me?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$O$1" Then
If Range("O1") = "Handle" Then
With ActiveSheet.ChartObjects("Chart 5").Chart
.Axes(xlValue).MaximumScale = 100000
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MajorUnit = 20000
End With
End If
If Range("O1") = "Games" Then
With ActiveSheet.ChartObjects("Chart 5").Chart
.Axes(xlValue).MaximumScale = 5000
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MajorUnit = 500
End With
End If
End If
End Sub


Also, the code in post #1 works if I type the values in manually, but not if they change because of formula?

[Juriemagic]

Yep, well, thanx for the help...I'll see if I can come right with a bit of googling. Thanx Aflatoon..

[Aflatoon]

You've already got the basic code... ;)

[Juriemagic]

That's right, should be fine soon..thanx..