Results 1 to 1 of 1
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    1
    Thanks
    1
    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:

    ChangeAxisScales()

    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

    End Sub

    This was tested in Excel 2011 on my MacBook Pro OS X 10.9.5 "Mavericks"

  2. 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!

    Excel 2013: The Missing Manual

    + 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!

  3. The Following User Says Thank You to sosinovitch For This Useful Post:

    Maudibe (2014-10-04)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •