VBA code to modify chart axes

[gmc_600]

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 contain dates. Initially I tried to develop some VBA code that could recognise which charts have dates on the x-axis but have settled for a simpler approach of checking that the value of the axis corresponds to a date between ~2021 and 2031 (Excel date codes 44197-47849). The code below works for charts that are embedded in worksheets but not charts that are in their own separate sheets:

Sub ChangeChartDates()

Dim iCht, number_of_columns, chart_sheets, worksheet_charts As Integer
Dim start_date As Date
Dim end_date As Date
Dim Msg As String

    'Chart date input
    Dim chart_start_date As Date
    Dim chart_end_date As Date
    start_date = Date
    end_date = Date
    start_date = DateAdd("m", -2, start_date) - Day(start_date) + 1
    end_date = end_date - Day(end_date) + 1
    chart_start_date = InputBox("Please select the start date.", "Start date?", start_date)
    chart_end_date = InputBox("Please select the end date.", "End date?", end_date)

    '====================
    'Ask for either chart sheets or charts that are objects in worksheets
    chart_sheets = 0
    selection_event = MsgBox("You can change charts in chart sheets or those embedded in worksheets. Would you like to change charts in chart sheets?", vbYesNo, " Which charts?")
    Select Case selection_event
    Case 6
        chart_sheets = 1
    End Select
    worksheet_charts = 0
    selection_event = MsgBox("Would you like to change charts embedded in worksheets?", vbYesNo, " Which charts?")
    Select Case selection_event
    Case 6
        worksheet_charts = 1
    End Select
    '====================

If chart_sheets = 1 Then
    '====================
    For Each oChart In ActiveWorkbook.Charts
        'Edit the x-axis
        With oChart
            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
                .Chart.Axes(xlCategory).MaximumScale = chart_end_date
                .Chart.Axes(xlCategory).MinimumScale = chart_start_date
            Else
            End If
        End With
    Next oChart
    '====================
End If

If worksheet_charts = 1 Then
    Dim sht As Worksheet
    Dim CurrentSheet As Worksheet
    Dim cht As ChartObject
    
    For Each sht In ActiveWorkbook.Worksheets
        For Each cht In sht.ChartObjects
        'Edit the x-axis
        With cht
                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
                    .Chart.Axes(xlCategory).MaximumScale = chart_end_date
                    .Chart.Axes(xlCategory).MinimumScale = chart_start_date
                Else
                End If
        End With
        Next cht
    Next sht
End If

End Sub

• Device and OS platform, PC/Windows 10
• Excel product name and version number Excel for Microsoft 365 MSO 32-bit, Version 2009 (Build 13231.20514 Click-to-Run)

Thanks

[#2]

@gmc_600 

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

[#3]

@Hans Vogelaar OK, attached.

Thanks

[#4]

@Hans Vogelaar 

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

[#5 · Accepted solution]

@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

[#6]

Thanks, works perfectly now!