I want to set the minimum and maximum values for the values of a stacked bar chart's x-axis to the values specified by a pair of cells on the worksheet in which the chart is located.
X17 has the minimum value ~41000 since I'm displaying dates
X18 has the maximum value ~41500
I have the following in the Worksheet Change area in VBA for the Worksheet in question, but when I change a cell in the sheet, nothing happens. I know the code is running, since I can put a break in and it stops, but it doesn't have any effect on the chart.
The code will run no matter which cell is changed, and it shouldn't care what cell is changed, it will always be using X17 and X18 as inputs, and changing the min & max values accordingly, correct? So I can change cell A1 and it will set (or re-set) the axis max/min to these values?
Thanks in advance for any assistance! This is my first attempt at using Select Case and Worksheet Change code
Dave
X17 has the minimum value ~41000 since I'm displaying dates
X18 has the maximum value ~41500
I have the following in the Worksheet Change area in VBA for the Worksheet in question, but when I change a cell in the sheet, nothing happens. I know the code is running, since I can put a break in and it stops, but it doesn't have any effect on the chart.
The code will run no matter which cell is changed, and it shouldn't care what cell is changed, it will always be using X17 and X18 as inputs, and changing the min & max values accordingly, correct? So I can change cell A1 and it will set (or re-set) the axis max/min to these values?
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$x$18"
ActiveSheet.ChartObjects("Chart 4").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case "$x$17"
ActiveSheet.ChartObjects("Chart 4").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case Else
End Select
End Sub
Thanks in advance for any assistance! This is my first attempt at using Select Case and Worksheet Change code
Dave