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