Creating Chart with VBA, can't format X-Axis as Text

Question

I am working on creating a macro that generates a chart.

The chart creation works as I expected, no issues there. The only problem I have is that the dates being shown in the X-Axis are incorrect.

Sub generateChart()
' Select a range starting in row 2.
' This macro will use that range, and create a chart just for them.
Dim rng As Range
Dim randR As Long, randG As Long, randB As Long

Set rng = Selection

Dim numCharts As Long
numCharts = ActiveSheet.ChartObjects.Count

Dim newChart As ChartObject

Dim num As Long
num = rng.Columns.Count

Dim i       As Long

For i = 1 To num
    randR = Application.WorksheetFunction.RandBetween(1, 200)
    randG = Application.WorksheetFunction.RandBetween(0, 255)
    randB = Application.WorksheetFunction.RandBetween(0, 255)

    With ActiveSheet
        Set newChart = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=75, Height:=225)
        With newChart.Chart
            .ChartType = xlXYScatterLines
            Debug.Print rng.Address

            .SetSourceData Source:=rng

            With .FullSeriesCollection(1)
                .Name = Cells(1, rng.Columns(i).Column).Value
                .Values = Range(Cells(2, rng.Columns(i).Column), _
                                Cells(rng.Rows.Count + 1, rng.Columns(i).Column))
                .XValues = "=Sheet2!$J$2:$J$10"
                .Format.Fill.ForeColor.RGB = RGB(randR, randG, randB)
                .Format.Line.Visible = msoFalse
                .MarkerStyle = 1
                .MarkerSize = 8
            End With

            .SeriesCollection.NewSeries
            With .FullSeriesCollection(2)
                .Name = "=Sheet2!$Q$1"
                .Values = "=Sheet2!$Q$2:$Q$10"
                .XValues = "=Sheet2!$J$2:$J$10"
                .Format.Line.Visible = msoTrue
                .MarkerStyle = 0
            End With

            .SetElement (msoElementLegendBottom)

            ' Add titles
            Dim titleStr As String
            .SetElement (msoElementChartTitleAboveChart)
            titleStr = Cells(1, rng.Columns(i).Column).Value & " Time Delay"

            With .ChartTitle
                .Text = titleStr
                .Format.TextFrame2.TextRange.Characters.Text = Cells(1, rng.Columns(i).Column).Value & " Time Delay"
                .Format.TextFrame2.TextRange.ParagraphFormat.TextDirection = msoTextDirectionLeftToRight
                .Format.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
            End With

            ' Now, hide the points that are 0 value
            hideZeroValues newChart

            ' I thought this would work, but it doesn't seem to do anything
            .Axes(xlCategory).CategoryType = xlCategoryScale

        End With 'newchart.chart

    End With                 ' ActiveSheet
Next i

End Sub

And a screenshot: Note that I don't even have the option to format as text.

(Note the averages are correct, there's hidden columns)

However! If I create a chart with the "built-in" chart, just by selecting the data, I can choose to format as text.

What am I overlooking in my Macro? Why can't I seem to set the X-Values correctly? Choosing "Number", then formatting as Date category keeps the incorrect dates. Finally, and perhaps it's a hint at what's going wrong, if I right click the chart, and try to Select Date, the "Horizontal Axis" is greyed out.

Thanks for any thoughts/ideas!

Edit: Here's a link to a .gif, showing the formatting working correctly, if I insert the chart via Excel's chart menu

Answer 1

I think what you're seeing is the difference between a Categorical x-axis and a Continuous one. "Scatter"-type plots use Continuous axes (ie. they plot the "range" of data, not just the individual points, and the displayed dates are determined by the major/minor tick intervals).

You should use a "regular" line chart (not the "scatter" version) and if it still doesn't behave then:

newChart.Chart.Axes(xlCategory).CategoryType = xlCategoryScale

should force the x-axis to Categorical mode