Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Using VBA to set Axis on Charts Issue in Excel 2007

    I'm new to this forum and VBA programming. I appreciate any help with an issue discussed previously discussed using VBA code to set the axes in charts. I have similar code that works in a different report template, but this report has 24 charts, named Chart_1 thru Chart_24 and the charts have both primary and secondary scales which need setting. The problem I'm having is that although the code runs without errors, it is not setting the minimum and maximum values that are contained in reference cells. If someone can spot the problem, I would be very grateful.
    Code:
    Sub AutoScale_Quantum()
    '
    ' AutoScale_Quantum Macro
    ' AutoScale Charts in Quantum Benchmark Report
    '
     
        With ActiveSheet.ChartObjects("Chart_1").Chart
        'Sets Secondary Horizontal Axis to reference numbers in D86 and C86
            With .Axes(xlValue, xlPrimary)
                .MaximumScale = ActiveSheet.Range("D86").Value
                .MinimumScale = ActiveSheet.Range("C86").Value
            End With
        'Sets Secondary Vertical Axis to reference numbers in E86 and F86
            With .Axes(xlValue, xlSecondary)
                .MaximumScale = ActiveSheet.Range("F86").Value
                .MinimumScale = ActiveSheet.Range("E86").Value
            End With
        'Sets the Primary Vertical Axis to Zero Min, Automatic Max
            With .Axes(xlValue)
                .MaximumScale = True
                .MinimumScale = 0
            End With
        End With
     
    Similar code continues with Chart_2, etc... until:
     
    End Sub
    Last edited by RetiredGeek; 2015-01-02 at 20:26. Reason: Added Code Tags

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    bdehem,

    The first section of your code sets the primary vertical axis max and min values. (Although your comment states horizontal, it is vertical).

    The second section sets the secondary vertical axis max and min values.

    The third section re-establishes the primary vertical axis max and min values overwriting section 1 and setting them to automatic. You have omitted the axes group in the statement .Axes(xlValue) and is by default primary


    My suggestion is to comment out section 3 if you want to customize the primary vertical axis max and min values.

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

    bdehm (2015-01-04)

  4. #3
    New Lounger
    Join Date
    Jan 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Maudible - Thanks so much for your reply. I understand your analysis, but your prescription didn't work because I left out too many details. I'm including my spreadsheet here for you to see.

    Referring to Chart_1 (If I can get one to work, the rest will too.)

    The third section is needed to define the height of the vertical (value) axis (the frequency number).

    The second section should control the vertical height on the secondary axis of the "Avg" and "You" data points (the blue and red bars) which are plotted on the secondary horizontal axis.

    The first section should refer to the Secondary Horizontal Axis which needs to be set to the Mi and Max of the data set defined as the "bins" value range.

    P.S. Now that I've written this I'm officially a dummy because I cannot find the button to upload a file with this comment.

  5. #4
    New Lounger
    Join Date
    Jan 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Using VBA to set Axis on Charts Issue in Excel 2007

    I figured it out.


    Here is the Excel 2007 file with charts 1-8 that need VBA codes to set up the axes values secondary horizontal and vertical axes.

    Bruce
    Attached Files Attached Files

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    bdehm,

    Your explantion makes it more clear. The first section, as you have it set up on your code, sets the min and max on the primary vertical axis. It is also reset to automatic in the 3rd section. To do what you want to do, change your first section to the line indicated in blue.

    HTH,
    Maudibe

    Code:
    Sub AutoScale_Quantum()
        With ActiveSheet.ChartObjects("Chart_1").Chart
        'Sets Secondary Horizontal Axis to reference numbers in D86 and C86
            With .Axes(xlCategory, xlSecondary)
                .MaximumScale = ActiveSheet.Range("D86").Value
                .MinimumScale = ActiveSheet.Range("C86").Value
            End With
            
        'Sets Secondary Vertical Axis to reference numbers in E86 and F86
            With .Axes(xlValue, xlSecondary)
                .MaximumScale = ActiveSheet.Range("F86").Value
                .MinimumScale = ActiveSheet.Range("E86").Value
            End With
        'Sets the Primary Vertical Axis to Zero Min, Automatic Max
            With .Axes(xlValue)
                .MaximumScale = True
                .MinimumScale = 0
            End With
        End With
    End Sub

  7. #6
    New Lounger
    Join Date
    Jan 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That fixed it. It really is amazing how one small missing detail can take days to resolve. Thank-you so much. I hope I can return the favor some day.
    Bruce

Posting Permissions

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