Touble with Dynamix Axis Scales via a Cell Value

[Idgarad]

I have had one heck of a time trying to implement the following code
snippet from http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

---SNIP---
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$E$2"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case "$E$3"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case Else
End Select
End Sub
--- SNIP ---


I assumed to the best of my ability the following

---SNIP---
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$E$2"
chart4.ChartObjects("CHART-Drawdown").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case "$E$3"
chart5.ChartObjects("CHART-Sliding
Average").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case "$F$2"
chart4.ChartObjects("CHART-Drawdown").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case "$F$3"
chart5.ChartObjects("CHART-Sliding
Average").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value

Case Else
End Select
End Sub
---SNIP---

The trouble is that it is originally written to work with an embedded
chart on the same page. I tried converting it to handle Chart sheets
but to no avail. What needs to be correctly modified to reference a
chart page?

 

[John]

Idgarad,

An embedded chart is contained in a "ChartObject", whilst a chart worksheet
IS a "Chart" object. So try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim chartSheet As Chart
Select Case Target.Address
Case "$E$2"
Set chartSheet = ThisWorkbook.Worksheets("Drawdown")
chartSheet.Axes(xlCategory) _
.MaximumScale = Target.Value

You'd probably benefit from some checking to ensure that the user hasn't
changed the chart tab name etc. but hopefully this gives you the idea.

Best regards

John

 

[Jon Peltier]

Set chartSheet = ThisWorkbook.Worksheets("Drawdown")

This has caused errors for Idgarad because a chart sheet is not a member of
the Worksheets collection.

Simplify it:

Select Case Target.Address
Case "$E$2"
Charts("CHART-Drawdown").Axes(xlCategory) _
.MaximumScale = Target.Value

- Jon

 

[John]

Morning,

Sorry Idgarad, of course that's quite right. Had temporary confusion
between the Sheets and Worksheets collections.

Thanks for the correction Jon.

Best regards

John