Update Chart Titles with Worksheet_Change Event

[terryspencer2003]

I have a chart sheet called ("Histogram")with an active X dropdown box
which pulls in data from Sheet1. The Active X dropdown list sends a
number to a named cell ("ChartNumber") on Sheet1 which is then used
within 2 Index formulas to create an active range for my X and Y range
to feed my chartsheet("Histogram").

I am trying to update my chart X axis title and my X axis format using
a Worksheet_Change Event within Sheet1. I have four basic titles and
formats which I have put inside a Case Statement. However I cannot
seem to make it work. I want the Worksheet_Change Event to trigger
when the value in named range ("ChartNumber") on Sheet1 changes. What
am I doing wrong?


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ChartScroll As Integer

Application.EnableEvents = True

ChartScroll = Range("xlChartNumber").Value

Select Case ChartScroll

Case 1 To 11 ', 23 To 33
Sheets("Histogram").Activate
Sheets("Histogram").Select
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0_);[Red](0)"
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Characters.Text = "Dollars ($000)"
Selection.AutoScaleFont = False

Case 12 To 22 ', 38 To 45, 34, 37
Sheets("Histogram").Activate
Sheets("Histogram").Select
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "#,##0_);[Red](#,##0)"
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Characters.Text = "MWh"
Selection.AutoScaleFont = False

Case Is = 35
Sheets("Histogram").Activate
Sheets("Histogram").Select
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0%"
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Characters.Text = "Variable"
Selection.AutoScaleFont = False

Case Is = 36
Sheets("Histogram").Activate
Sheets("Histogram").Select
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0.00"
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Characters.Text = "Variable"
Selection.AutoScaleFont = False

End Select


End Sub

 

[Jon Peltier]

Check out my response in the programming group.

Also, the combobox has its own set of events you can tap into. Right
click on the control, select View Code, and choose the event you want
from the right hand dropdown at the top of the code window. Or use the
default Control_Change event.

- Jon