• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using macro to change graph axis... a little lost

M

mvirgilio53

New Member
Hello, I'm working on a dashboard that includes some charts that are changed via drop-down lists. That's simple enough, but the problem is the chart axis and the automatic scaling. There isn't a good fixed minimum that I can set the axis to, so I will need a macro to change the scale based on a formula. I found this example of a macro:


Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address

Case "$E$2"

ActiveSheet.ChartObjects("Chart 22").Chart.Axes(xlCategory) _

.MaximumScale = Target.Value

Case "$E$3"

ActiveSheet.ChartObjects("Chart 22").Chart.Axes(xlCategory) _

.MinimumScale = Target.Value

Case "$E$4"

ActiveSheet.ChartObjects("Chart 22").Chart.Axes(xlCategory) _

.MajorUnit = Target.Value

Case Else

End Select

End Sub


I get an error when I change the cell that should activate the macro and I'm not really sure how to trouble-shoot. Thank you!
 
The macro is assuming that you have 3 cells:

E2 - Max Scale

E3 - Min Scale

E4 - Major Unit

The code must be put in a VBA Code page for the worksheet containing the chart


The code will run automatically when ever anything on the worksheet changes


If the cell that has changed is one of the cells above the code will attempt to find "Chart 22" and set the appropriate property


Select your chart

The Charts name will be shown in the name box just above the Cell A1

Is your chart named "Chart 22"? if not rename it or change the macro to suit


I would also check that the maximum scale is more than the minimum scale


If the above doesn't help what error message are you getting?
 
Thanks for your response.


Well, last night when I was working on it, it was giving me an error message. I since changed the 'Chart.Axes(xlCategory)' code to 'Chart.Axes(xlValue)'. I thought that would make a difference, but now nothing happens when I change a cell on the page.


Here is the updated macro:


Option Explicit


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
 
This works for me

[pre]
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
[/pre]
Make sure when you copy code off web sites that the " and other similar characters come across correctly, they don't always

If unsure type a new one next to one and see if there is a visible difference
 
It doesn't seem to be doing anything. Do I need the "Option Explicit" at the top of the VBA code sheet? Would it make a difference that the chart has 2 axis?
 
What Version of Excel are you using ?

No you don't need to have option explicit


The Second Series wont effect it


The Code needs to be on the code sheet for the spreadsheet page which contains the chart
 
So I think I understand what's going on. Since the cells containing the min/max/major units are based on formulas from another data source, the macro isn't recognizing that the cell are changing even when the formula results change. When I go to the cells containing the min/max/major unit and click on the formula and press enter, the macro works and graph axis changes. The graph's contents change when I select from a drop-down list, so is there any way to run the macro when the cell in that drop-down list changes?


Thanks again for all your help.
 
I just don't understand why the code doesn't run when the results of the formulas in the cells change. Isn't that the whole point of the "change event" type macro?
 
Target is the cell/s that changes on the worksheet when the Worksheet_Change event occurs, not the cells based on the changed cells


So using Target.Address in your Select Case is forcing it to only check when 3 specific cells change


So try the following code instead

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.ChartObjects("Chart 22").Chart.Axes(xlValue).MaximumScale = [AC14]
ActiveSheet.ChartObjects("Chart 22").Chart.Axes(xlValue).MinimumScale = [AC15]
ActiveSheet.ChartObjects("Chart 22").Chart.Axes(xlValue).MajorUnit = [AC16]

End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top