PDA

View Full Version : chart with dynamic axis help



nutttom
10-14-2015, 04:55 AM
Hi can someone help?

I'm trying to change the max and min x and y axis on a chart by entering the max and min values in a cell in a different sheet to that of where the chart is located.

This code is working i.e. cells from the same sheet:


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

End Sub




This code doesn't work when refering cells from another sheet:


Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet.ChartObjects("Chart 3").Chart
Select Case Target.Address
Case ActiveWorkbook.Worksheets("CALCULATOR").Cells(8, 3).Value
.Axes(xlCategory).MaximumScale = Target.Value
Case ActiveWorkbook.Worksheets("CALCULATOR").Cells(7, 3).Value
.Axes(xlCategory).MinimumScale = Target.Value
Case ActiveWorkbook.Worksheets("CALCULATOR").Cells(5, 1).Value
.Axes(xlCategory).MajorUnit = Target.Value
Case ActiveWorkbook.Worksheets("CALCULATOR").Cells(8, 7).Value
.Axes(xlValue).MaximumScale = Target.Value
Case ActiveWorkbook.Worksheets("CALCULATOR").Cells(7, 7).Value
.Axes(xlValue).MinimumScale = Target.Value
Case ActiveWorkbook.Worksheets("CALCULATOR").Cells(5, 2).Value
.Axes(xlValue).MajorUnit = Target.Value
End Select
End With

End Sub




Thanks
Tom

p45cal
10-14-2015, 05:18 AM
Your first code needs to be in the CALCULATOR sheet code-module with a tweak:
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("TheNameOfTheSheetWithTheChartOnIt").ChartObjects("Chart 3").Chart
Select Case Target.Address
Case "$B$2"
.Axes(xlCategory).MaximumScale = Target.Value
Case "$B$3"
.Axes(xlCategory).MinimumScale = Target.Value
Case "$B$4"
.Axes(xlCategory).MajorUnit = Target.Value
Case "$C$2"
.Axes(xlValue).MaximumScale = Target.Value
Case "$C$3"
.Axes(xlValue).MinimumScale = Target.Value
Case "$C$4"
.Axes(xlValue).MajorUnit = Target.Value
End Select
End With
End SubObviously it needs one further tweak from you.

nutttom
10-14-2015, 06:01 AM
Thank you!!!!



I love this forum.