I have a Worksheet Change VBA code added to my Worksheet in excel. Basically I want to change the scaling in a chart within the worksheet when I change a cell value. This code works
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet.ChartObjects("Chart 1").Chart
Select Case Target.Address
Case "$B$5"
.Axes(xlCategory).MaximumScale = ActiveSheet.Range("XMax").Value
Case "$B$6"
.Axes(xlCategory).MinimumScale = ActiveSheet.Range("XMin").Value
Case "$C$5"
.Axes(xlValue).MaximumScale = ActiveSheet.Range("YMax").Value
Case "$C$6"
.Axes(xlValue).MinimumScale = ActiveSheet.Range("YMin").Value
End Select
End With
End Sub
But I would like to use the cell names in the CASE statement rather than the $B$5 so if someone adds rows or columns to the worksheet the VBA will still run properly. As you can see in the code $B$5 is named XMax in the worksheet and $B$6 is named XMin and so on. But if I use Case "XMax" or Case Range("XMax") the VBA doesn't work.
I'm hoping someone can tell me what I'm doing wrong. Any help would be greatly appreciated.
Bookmarks