VBA code If statement to check if chart x-axis is formatted as Date

[gmcgough600]

Hi, looking for some help writing some VBA code.

I need to write an If statement to check if the x-axis of a chart is formatted as a date (doesn't matter if its dd-mmm-yy or dd/mm/yyyy, mm/dd/yyyy etc) then run some other code. Here's what I've got so far:

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
            [B]'[help needed here] If 'chart has date x-axis' Then[/B]
                .Chart.Axes(xlCategory).MaximumScale = chart_end_date
                .Chart.Axes(xlCategory).MinimumScale = chart_start_date
            Else if
                'Do nothing
            End if
        End With
        Next cht
    Next sht
End If

Hope someone can help, thanks!

 

[gmcgough600]

I just noticed you said value axis - why are you checking those for dates?

The dates are on the x-axis, is this not classed as a value axis? When I hover over it says "Horizontal (Value) Axis". Not completely clear on the terminology here as hovering over the y-axis says "Vertical (Value) Axis". How do you differentiate between x and y in the code?

 

[bmurphy]

The test on majorunitscale did not work for me on an xy scatter chart whose x axis was displaying tick labels as Dates. So I changed the test to:
If InStr(ax.TickLabels.NumberFormat, "yy") Then IsDateAxis = True
So far this has worked for the charts I have tested it on.

 

[Jon Peltier]

Thanks RoryA, what's the easiest way to check this property? Probably very simple but I gave this a try and got stuck:

        With cht
            With .Chart.Axes(xlCategory)
                If .MajorUnitScale = xlDays Then

I'm getting error "Run-time error '-2147467259 (80004005)':

This property is not used by value axes."

The chart I'm testing on has dates on the x-axis.

Your chart is an XY Scatter chart. Dates/times on the X-axis are plotted as values.

MajorUnitScale is only a property of a non-XY chart's X-axis (say, a line chart, not a scatter chart) if the axis scale shows dates.