Macro to control chart axis

[zeppelinmv]

Hello everyone. I am trying to create a macro that will adjust a chart axis based on some formulas. The macro I've been tinkering with works only if I click within the cells with the desired range and hit 'enter'. My understanding of a change_event type macro was that it was run whenever any cells in the sheet change, but that doesn't seem to be happening.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$AC$14"
ActiveSheet.ChartObjects("Chart 22").Chart.Axes(xlValue).MaximumScale = Target.Value
Case "$AC$15"
ActiveSheet.ChartObjects("Chart 22").Chart.Axes(xlValue).MinimumScale = Target.Value
Case "$AC$16"
ActiveSheet.ChartObjects("Chart 22").Chart.Axes(xlValue).MajorUnit = Target.Value
Case Else
End Select
End Sub

The data for the chart is controlled via a drop-down menu, so I would like the macro to run whenever a choice from the drop-down is selected.

Thank you. Great site.

 

[AlphaFrog]

What kind of drop-down are you using?

• Data Validation
• ActiveX ComboBox
• Forms ComboBox

If you are using a Data Validaion drop-down list, your Worksheet_Change event macro should work.

If you have linked a cell to either one of the other ComboBoxes, you would have to trigger your code with a different method.

ActiveX ComboBox has its' own event procedures e.g;

Private Sub ComboBox1_Change()

End Sub

The Forms ComboBox can have a Macro assigned to it to trigger when you make a change.

 

[zeppelinmv]

It's a data validation drop-down.

 

[AlphaFrog]

Do you have three Data Validation drop-downs in cells AC14, AC15, and AC16 ? If No, where are the Data Validation drop-downs and where are the formulas?