Touble with Dynamix Axis Scales via a Cell Value

I

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?
 
J

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
 
J

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scaling Graphs 9
Scaling Charts 3
Axes(xlCategory) for a chart-sheet in 2007 2
add a chart in a Add-In 1
Axis scaling - Jon's code 2
Excel 2007 Rect type problem 1
dynamic chart range with VBA 6
secondary Y axis 1

Top