Forum Discussion

Copper Contributor

Jun 09, 2021

Solved

VBA code to modify chart axes

Hi, looking for some help. I'm trying to develop an add-in (xlam file) that allows me to modify the x-axis of all the charts in the open workbook. I'm specifically trying to change axes which cont...

Jun 11, 2021

Thanks! The cause of the error is that an embedded chart (chartobject) on a worksheet contains a Chart object, while a chart sheet is a Chart object. So you don't need the .Chart for chart sheets:

If chart_sheets = 1 Then
    '====================
    For Each oChart In ActiveWorkbook.Charts
        'Edit the x-axis
        With oChart
            If (.Axes(xlCategory).MinimumScale > 44000) And (.Axes(xlCategory).MinimumScale < 47849) And (.Axes(xlCategory).MaximumScale > 44000) And (.Axes(xlCategory).MaximumScale < 47849) Then
                .Axes(xlCategory).MaximumScale = chart_end_date
                .Axes(xlCategory).MinimumScale = chart_start_date
            Else
                'Do nothing
            End If
        End With
    Next oChart
    '====================
End If

HansVogelaar

MVP

Jun 09, 2021

gmc_600

It would help if you attached a small sample workbook with both an embedded chart and a chart sheet.

gmc_600

Copper Contributor

Jun 11, 2021

HansVogelaar

The error I'm getting when I run the code for a chart sheet is:

"Run-time error '438':

Object doesn't support this property or method"

This occurs on the following line of code:

If (.Chart.Axes(xlCategory).MinimumScale > 44197) And (.Chart.Axes(xlCategory).MinimumScale < 47849) And (.Chart.Axes(xlCategory).MaximumScale > 44197) And (.Chart.Axes(xlCategory).MaximumScale < 47849) Then

HansVogelaar

MVP

Jun 11, 2021

gmc_600

Thanks! The cause of the error is that an embedded chart (chartobject) on a worksheet contains a Chart object, while a chart sheet is a Chart object. So you don't need the .Chart for chart sheets:

If chart_sheets = 1 Then
    '====================
    For Each oChart In ActiveWorkbook.Charts
        'Edit the x-axis
        With oChart
            If (.Axes(xlCategory).MinimumScale > 44000) And (.Axes(xlCategory).MinimumScale < 47849) And (.Axes(xlCategory).MaximumScale > 44000) And (.Axes(xlCategory).MaximumScale < 47849) Then
                .Axes(xlCategory).MaximumScale = chart_end_date
                .Axes(xlCategory).MinimumScale = chart_start_date
            Else
                'Do nothing
            End If
        End With
    Next oChart
    '====================
End If

gmc_600
Copper Contributor
Jun 14, 2021
Thanks, works perfectly now!