Macro to control chart axis

zeppelinmv

New Member
Joined
Sep 22, 2010
Messages
2
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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;
Code:
Private Sub ComboBox1_Change()

End Sub


The Forms ComboBox can have a Macro assigned to it to trigger when you make a change.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top