Y-axis automatic scale isn't appropriate but manual scaling also isn't

[gooniegirl180]

Hi everyone, I am attempting to graph headcounts by month by branch.

I have a dropdown box where the user can select the branch, which updates the underlying data and the chart accordingly. One of the branches is "ALL" which is the total headcount by month.

Where I am coming unstuck is the Y-axis scaling. If I select a smaller branch, it might show gridlines at, say, 5, 5.2, 5.4, 5.6, 5.8 and 6. However, being headcounts, they can only be whole numbers. If I change the formatting to have intervals of "1", as soon as I select "ALL" branches, you can't see the scale as there is a gridline for every number and you can't read anything!

How can I fix this?

Regards,
gooniegirl180

 

[Legacy 456129]

Been there, experienced that. Use a macro to set the stepping. I've got some calculations set up and named the various computed values which I feed into a script. The below may give you ideas. Several button on your sheet that fire different value calls for departmental and overall.

Private Sub AxisScaling() '19.01.2020 chg, 16.01.2020 chg ver 01.05.2017 Recalibrates the 2 Y Axes on the CumRes chart

      Dim chartidx As Byte, charts As ChartObject '15.01.2020
      Dim charttyp As Variant: charttyp = sALL.ChartObjects(1).Chart.charttype '15.01.2020
      For Each charts In sALL.ChartObjects '15.01.2020 -4111 is charttype xlCombination
         If charttyp = -4111 Then chartidx = charts.Index '15.01.2020
      Next charts '15.01.2020
10    With sALL
         .ChartObjects(chartidx).Name = "CumRes" '15.01.2020
15       Range("VarsCumRes").Calculate 'add 30.04.2017
20       vars = Range("VarsCumRes")
25      If vars(1, 1) <= Range("PriorCY").Value Then Exit Sub
40       If .ProtectContents = True Then ProtectStatus = True: .Unprotect Password:=ShtPw ''16.01.2020 chg, has to be on to be able to select chart elements.
50       .ChartObjects(chartidx).Activate
60    End With 'sAll
70       With ActiveChart.Axes(xlValue, xlPrimary)
80          .Select
90          .MaximumScale = vars(1, 1): .MinimumScale = vars(1, 2): .MajorUnit = vars(1, 3)
100      End With
110      With ActiveChart.Axes(xlValue, xlSecondary)
120         .MaximumScale = vars(1, 4): .MinimumScale = vars(1, 5): .MajorUnit = vars(1, 6)
130      End With
140      With ActiveChart.Axes(xlCategory)
            .TickLabelSpacing = Range("GraphLblIntrvl").Value
150         .TickMarkSpacing = Range("GraphLblIntrvl").Value
160      End With
170      Range("PriorCY") = vars(1, 1)
180 If ProtectStatus = True Then Run "SheetProtected", sALL '16.01.2020 add
190 Application.Goto "MainTitle" '19.01.2020 add to deselect chart
200
End Sub 'EoS AxisScaling

This one ensures the two Y-Axes are set so the line graph starts exactly bottom left, and ends exactly top right, with appropriate stepping.