2d & 3d charts present

[Gordon Smith]

I have both 2D and 3D stacked bar charts working from identical source data with identical horizontal axis format options set:
Min = Auto
Max = Auto
Major unit = Fixed: 0.3
Minor unit = Fixed: 0.3

Unfortunately the horizontal axis of the 2D chart is longer and has whitespace at the end of the plotted data (where 9.3 & 9.6 are), which I'd really like to eliminate.

The data is constantly changing so I cannot use a fixed value for the range maximum.

I cannot use a 3D chart as they do not support secondary axes and I wasn't able to get the vertical gridlines to line up very well.

(the 2D chart is at the bottom)

 

[pbornemeier]

Welcome to the Board!

This code will set the max Horizontal value of all 2D or 3D bar charts on the activesheet, based on the max row sum of the cells in rows 2 and 3.

Option Explicit

Sub SetHorizontalAxisMaxValue()

    Dim chtobj As ChartObject
    Dim sngMaxSum As Single
    Dim sngRowSum As Single
    Dim lX As Long
    
    With ActiveSheet
        'Find max value of summed row
        For lX = 2 To 3
            sngRowSum = Application.WorksheetFunction.Sum(.Rows(lX))
            If sngRowSum > sngMaxSum Then sngMaxSum = sngRowSum
        Next
        
        'Set max Horizontal to that value
        For Each chtobj In .ChartObjects
            Select Case chtobj.Chart.ChartType  'xlChartType
            Case xl3DBarStacked, xlBarStacked   '61, 58
                With chtobj.Chart
                    .Axes(xlValue).MaximumScale = sngMaxSum
                End With
            End Select
        Next
    End With
    
End Sub

 

[Gordon Smith]

This works very well, thank you for coding a solution.

Is there any way to accomplish this without using a macro?
I only ask in case there is a way to make this possible without showing my users a security warning.

 

[pbornemeier]

I don't know of a way to do it without the macro.
Have the users put the the excel file in a trusted location or or use the trust center to define its current location as trusted. If you are on a network that uses authentication, you can digitally sign the code (from the VBE editor Tools | Digital Signature) which should allow the users to identify your code as safe the first time they run it so they won't keep seeing the security warning.