SOLVED

VBA code to modify chart axes

%3CLINGO-SUB%20id%3D%22lingo-sub-2430177%22%20slang%3D%22en-US%22%3EVBA%20code%20to%20modify%20chart%20axes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2430177%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20looking%20for%20some%20help.%26nbsp%3BI'm%20trying%20to%20develop%20an%20add-in%20(xlam%20file)%20that%20allows%20me%20to%20modify%20the%20x-axis%20of%20all%20the%20charts%20in%20the%20open%20workbook.%20I'm%20specifically%20trying%20to%20change%20axes%20which%20contain%20dates.%20Initially%20I%20tried%20to%20develop%20some%20VBA%20code%20that%20could%20recognise%20which%20charts%20have%20dates%20on%20the%20x-axis%20but%20have%20settled%20for%20a%20simpler%20approach%20of%20checking%20that%20the%20value%20of%20the%20axis%20corresponds%20to%20a%20date%20between%20~2021%20and%202031%20(Excel%20date%20codes%2044197-47849).%20The%20code%20below%20works%20for%20charts%20that%20are%20embedded%20in%20worksheets%20but%20not%20charts%20that%20are%20in%20their%20own%20separate%20sheets%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20ChangeChartDates()%0A%0ADim%20iCht%2C%20number_of_columns%2C%20chart_sheets%2C%20worksheet_charts%20As%20Integer%0ADim%20start_date%20As%20Date%0ADim%20end_date%20As%20Date%0ADim%20Msg%20As%20String%0A%0A%20%20%20%20'Chart%20date%20input%0A%20%20%20%20Dim%20chart_start_date%20As%20Date%0A%20%20%20%20Dim%20chart_end_date%20As%20Date%0A%20%20%20%20start_date%20%3D%20Date%0A%20%20%20%20end_date%20%3D%20Date%0A%20%20%20%20start_date%20%3D%20DateAdd(%22m%22%2C%20-2%2C%20start_date)%20-%20Day(start_date)%20%2B%201%0A%20%20%20%20end_date%20%3D%20end_date%20-%20Day(end_date)%20%2B%201%0A%20%20%20%20chart_start_date%20%3D%20InputBox(%22Please%20select%20the%20start%20date.%22%2C%20%22Start%20date%3F%22%2C%20start_date)%0A%20%20%20%20chart_end_date%20%3D%20InputBox(%22Please%20select%20the%20end%20date.%22%2C%20%22End%20date%3F%22%2C%20end_date)%0A%0A%20%20%20%20'%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%0A%20%20%20%20'Ask%20for%20either%20chart%20sheets%20or%20charts%20that%20are%20objects%20in%20worksheets%0A%20%20%20%20chart_sheets%20%3D%200%0A%20%20%20%20selection_event%20%3D%20MsgBox(%22You%20can%20change%20charts%20in%20chart%20sheets%20or%20those%20embedded%20in%20worksheets.%20Would%20you%20like%20to%20change%20charts%20in%20chart%20sheets%3F%22%2C%20vbYesNo%2C%20%22%20Which%20charts%3F%22)%0A%20%20%20%20Select%20Case%20selection_event%0A%20%20%20%20Case%206%0A%20%20%20%20%20%20%20%20chart_sheets%20%3D%201%0A%20%20%20%20End%20Select%0A%20%20%20%20worksheet_charts%20%3D%200%0A%20%20%20%20selection_event%20%3D%20MsgBox(%22Would%20you%20like%20to%20change%20charts%20embedded%20in%20worksheets%3F%22%2C%20vbYesNo%2C%20%22%20Which%20charts%3F%22)%0A%20%20%20%20Select%20Case%20selection_event%0A%20%20%20%20Case%206%0A%20%20%20%20%20%20%20%20worksheet_charts%20%3D%201%0A%20%20%20%20End%20Select%0A%20%20%20%20'%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%0A%0AIf%20chart_sheets%20%3D%201%20Then%0A%20%20%20%20'%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%0A%20%20%20%20For%20Each%20oChart%20In%20ActiveWorkbook.Charts%0A%20%20%20%20%20%20%20%20'Edit%20the%20x-axis%0A%20%20%20%20%20%20%20%20With%20oChart%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20(.Chart.Axes(xlCategory).MinimumScale%20%26gt%3B%2044197)%20And%20(.Chart.Axes(xlCategory).MinimumScale%20%26lt%3B%2047849)%20And%20(.Chart.Axes(xlCategory).MaximumScale%20%26gt%3B%2044197)%20And%20(.Chart.Axes(xlCategory).MaximumScale%20%26lt%3B%2047849)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.Chart.Axes(xlCategory).MaximumScale%20%3D%20chart_end_date%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.Chart.Axes(xlCategory).MinimumScale%20%3D%20chart_start_date%0A%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20Next%20oChart%0A%20%20%20%20'%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%0AEnd%20If%0A%0AIf%20worksheet_charts%20%3D%201%20Then%0A%20%20%20%20Dim%20sht%20As%20Worksheet%0A%20%20%20%20Dim%20CurrentSheet%20As%20Worksheet%0A%20%20%20%20Dim%20cht%20As%20ChartObject%0A%20%20%20%20%0A%20%20%20%20For%20Each%20sht%20In%20ActiveWorkbook.Worksheets%0A%20%20%20%20%20%20%20%20For%20Each%20cht%20In%20sht.ChartObjects%0A%20%20%20%20%20%20%20%20'Edit%20the%20x-axis%0A%20%20%20%20%20%20%20%20With%20cht%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20(.Chart.Axes(xlCategory).MinimumScale%20%26gt%3B%2044197)%20And%20(.Chart.Axes(xlCategory).MinimumScale%20%26lt%3B%2047849)%20And%20(.Chart.Axes(xlCategory).MaximumScale%20%26gt%3B%2044197)%20And%20(.Chart.Axes(xlCategory).MaximumScale%20%26lt%3B%2047849)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.Chart.Axes(xlCategory).MaximumScale%20%3D%20chart_end_date%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.Chart.Axes(xlCategory).MinimumScale%20%3D%20chart_start_date%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20%20%20%20%20Next%20cht%0A%20%20%20%20Next%20sht%0AEnd%20If%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSTRONG%3EDevice%20and%20OS%20platform%3C%2FSTRONG%3E%2C%20PC%2FWindows%2010%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EExcel%20product%20name%20and%20version%20number%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3BExcel%20for%20Microsoft%20365%20MSO%2032-bit%2C%20Version%202009%20(Build%2013231.20514%20Click-to-Run)%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2430177%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2430333%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20modify%20chart%20axes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2430333%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1074549%22%20target%3D%22_blank%22%3E%40gmc_600%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20would%20help%20if%20you%20attached%20a%20small%20sample%20workbook%20with%20both%20an%20embedded%20chart%20and%20a%20chart%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439997%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20modify%20chart%20axes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439997%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BOK%2C%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

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

5 Replies

@gmc_600 

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

@Hans Vogelaar OK, attached.

 

Thanks

@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

 

best response confirmed by gmc_600 (New Contributor)
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

 

Thanks, works perfectly now!