Jun 09 2021 03:26 AM - edited Jun 09 2021 03:40 AM
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
Thanks
Jun 09 2021 04:28 AM
It would help if you attached a small sample workbook with both an embedded chart and a chart sheet.
Jun 11 2021 08:57 AM
Jun 11 2021 09:03 AM
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
Jun 11 2021 12:13 PM - edited Jun 11 2021 12:13 PM
SolutionThanks! 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
Jun 11 2021 12:13 PM - edited Jun 11 2021 12:13 PM
SolutionThanks! 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