|
Touble with Dynamix Axis Scales via a Cell Value
I have had one heck of a time trying to implement the following code
snippet from http://peltiertech.com/Excel/Charts/...nkToSheet.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? |
Touble with Dynamix Axis Scales via a Cell Value
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 from http://peltiertech.com/Excel/Charts/...nkToSheet.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? |
Touble with Dynamix Axis Scales via a Cell Value
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John" wrote in message ... 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 from http://peltiertech.com/Excel/Charts/...nkToSheet.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? |
Touble with Dynamix Axis Scales via a Cell Value
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 "Jon Peltier" wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John" wrote in message ... 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 from http://peltiertech.com/Excel/Charts/...nkToSheet.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? |
| All times are GMT +1. The time now is 09:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com