2014-09-27, 13:15 #1
- Join Date
- Sep 2014
- Thanked 1 Time in 1 Post
Excel VBA Macro to set chart axis max and min values using max/min formulas
In response to the suggestions in this thead, http://windowssecrets.com/forums/sho...orskheet-cells, I wanted to show others how I adjusted my chart axis min/max value by using a formula function instead of a predefined value.
I edited the code in the workbook posted by zeddy to specify the actual min and max of the values being plotted and added/subtracted from those values as follows:
Dim objCht As ChartObject
For Each objCht In ActiveSheet.ChartObjects 'loop through all charts on sheet
With objCht.Chart.Axes(xlValue) 'define shortcut
.MaximumScale = [max(C18:C25)] + 0.05 'set value from max of a specified range plus a value (in this case 0.05)
'I used the max function to calculate the maximum of the values being plotted and added .05 to allow for space above that max value.MinimumScale = [min(C18:C25)] - 0.05 'set value from min of a specified range minus a value (in this case 0.05)
'I used the min function to calculate the minimum of the values being plotted and subtracted .05 to allow for space below that min value'.MajorUnit = [Unit] 'set value from named cell (I commented this out because I didn't use it ... but others might find it helpful)
End With 'end of shortcut
Next objCht 'process next chart
This was tested in Excel 2011 on my MacBook Pro OS X 10.9.5 "Mavericks"
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
The Following User Says Thank You to sosinovitch For This Useful Post: