• 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.

Setting Y-Axis Minimums Automatically

I have a line chart with several series. There are small value differences between the series, and I want to highlight those differences. Excel is automatically setting the y-axis value starting at zero. I can manually alter the axis by entering a minimum value so that the axis starts at say, 40,000. But these charts will be continually updated, so I need an automatic way of setting the minimum value in accord with the data series.upload_2017-7-12_14-31-5.png
Any ideas?
Thanks
 
You can use a small piece of code to assist you here
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   
If Target.Address <> "$E$1" Then Exit Sub
   
Application.EnableEvents = False

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MinimumScale = Range("E1")

Application.EnableEvents = True

End Sub

Copy the code into a Code Module for the worksheet you are using
Change the Cell Reference and Chart Name to suit
 
Thanks a lot! There are a bunch of graphs on the attached sheet. You can just do the code for the top left one and I should be able to reproduce for the other charts.
Paul
 

Attachments

  • Trend Analysis.xlsb
    649.7 KB · Views: 1
see attached

The yellow block of values correspond with the charts locations

check out the VBA to understand how it works
 

Attachments

  • Trend Analysis.xlsb
    643.8 KB · Views: 8
Thanks for this. I see the code you entered in
VBA Project (Trend Analysis.xlsb)>Microsoft Excel Objects>Sheet8 (Trend Analysis).
There is also code in
VBA Project (Trend Analysis.xlsb)>Modules>Module1
I don't know the reason behind this second location of code.

To duplicate this in my full file, I reset the scale of the first graph to auto, which is zero in this particular graph. I navigated away to another tab and then back again to see if the minimum would be set to 50,000, but it remained at zero. I saved the file and retrieved it to see if the code would kick in, but the scale still remained at zero. Is there a triggering event or something I'm missing?
 
You can delete the Macro in Module1
Change the values of any of the yellow cells and the charts will update
You have to accept Macro's when the file opens
 
Back
Top