Excel VBA Chart.Axes(xlValue).MajorUnit

[PeterMo]

Hi, I have an Excel 2010 macro/program that produces 80 or so reports using a standard report template. On the report are 2 piecharts, 4 histograms and 2 bar charts. Some of the quantities are of the order of several hundred, but others are less than 5. All of the quantities are integer. I have formatted the values on the value axis of the histograms and bar charts not to have any decimal places. This is fine for large quantities, but when they are small Excel sets the Major Unit to be 0.5 or less. The values on the axis then show as something like 1, 1, 2, 2, 3 because of the rounding. I’ve therefore included a routine that sets the MajorUnit to be 1 when Excel has decided that it should be less than this. Most of the time this works,but not always. It doesn’t fail consistently on the same report or the same chart within a report. However, it always fails with the same error: -2147467259 Chart Layout Failed

I’ve included the routine below and highlighted the statement that fails. Any suggestions what I’ve missed?

Thanks & regards

Peter
'===================================================================
Private Sub FixMajorUnits(wsReport AsWorksheet)
Dim coChartObject As ChartObject
Dim chChart As Chart
Dim sError As String

On Error GoTo labError

For Each coChartObject In wsReport.ChartObjects
SetchChart = coChartObject.Chart
PushObj chChart
IfchChart.HasAxis(xlValue) Then
If chChart.Axes(xlValue).MajorUnit< 1 Then
chChart.Axes(xlValue).MajorUnitIsAuto = False
chChart.Axes(xlValue).MajorUnit = 1
End If
EndIf
PopObj chChart
SetchChart = Nothing
Next coChartObject

Exit Sub

labError:

sError = "Error: " & Err.Number& " " & Err.Description & " - " &chChart.Name & " - " & wsReport.Name
Debug.Print sError
Resume Next

End Sub

[Dave]

This seems to work. Adjust to suit. Dave
[VBA]
With Worksheets("Sheet1")
For cnt = 1 To .ChartObjects.Count
If .ChartObjects(cnt).Chart.Axes(xlValue).MajorUnit < 1 Then
.ChartObjects(cnt).Chart.Axes(xlValue).MajorUnitIsAuto = False
.ChartObjects(cnt).Chart.Axes(xlValue).MajorUnit = 1
End If
Next cnt
End With
[/VBA]

[PeterMo]

Thanks Dave. I tried your code adjusted to suit and still got the same problem unfortunately. As I say, it works most of the time. Just now and again it decides to throw its teddy out of the pram. Peter

[Dave]

Is the error chart specific or random...maybe add a msgbox in the routine to find out the chart number that it errors on (ie. cnt). The only thing I can suggest is to identify and replace any chart that it errors on. HTH. Dave

[werafa]

You might also like to pull up the locals window when you get the error.