Touble with Dynamix Axis Scales via a Cell Value
 

On May 23, 10:54 am, "John" wrote:
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

"Idgarad" wrote in message

oups.com...



I have had one heck of a time trying to implement the following code
snippet fromhttp://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?- Hide quoted text -

- Show quoted text -

Tried but get a subscript error (The cart names via the chart tab is
actually CHART-xxxx but via the object browser show up as Chart4
(CHART-xxxxx) and Chart5 (CHART-XXXXX) respectivly):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim chartSheet As Chart
Select Case Target.Address
Case "$B$22"
Set chartSheet = ThisWorkbook.Worksheets("CHART-Drawdown")
chartSheet.Axes(xlCategory) _
.MaximumScale = Target.Value
Case "$b$26"
Set chartSheet = ThisWorkbook.Worksheets("CHART-Sliding
Average")
chartSheet.Axes(xlCategory) _
.MaximumScale = Target.Value
End Select
End Sub