Date/Time Category Axis

[kdave]

Hello All,

I am using Jon Peltier's instructions for creating a Gantt chart with repeated values. (Found at: Easier Gantt Chart for Repeated Tasks - Peltier Tech Blog). It works perfectly, except...

Except when I use a macro to create the chart. The line that I'm having problems with is the one that turns the category axis into a date. It just doesn't work in the macro. In fact, it sort of works. The format dialog box changes the option button to the date category, but the rest of the dialog box looks as if it is still a text format. The code that creates the chart is below. Any one have any ideas?

Code:

Cells(1, 20).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlBarStacked
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Imported Data'!$M$1"
ActiveChart.SeriesCollection(1).Values = _
    "='Imported Data'!StartDate"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Imported Data'!$N$1"
ActiveChart.SeriesCollection(2).Values = _
    "='Imported Data'!Duration"
ActiveChart.SeriesCollection(2).XValues = _
    "='Imported Data'!Truck"
ActiveChart.Axes(xlCategory, xlPrimary).Select
ActiveChart.Axes(xlCategory, xlPrimary).ReversePlotOrder = True
ActiveChart.Axes(xlCategory, xlPrimary).Crosses = xlMaximum
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
ActiveChart.Axes(xlValue, xlPrimary).Select
Selection.TickLabels.NumberFormat = "m/d/yyyy"
ActiveChart.Axes(xlValue).MinimumScale = bDate
ActiveChart.Axes(xlValue).MaximumScale = eDate
ActiveChart.Axes(xlValue).MajorUnit = 1
ActiveChart.ChartArea.Select
Sheets("Ind Trucks").Select
LR = LastUsedRow(ActiveSheet, 11)
Range("J1:K" & LR).Select
Selection.Copy
Sheets("Imported Data").Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.Paste
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).AxisGroup = 2
ActiveChart.SeriesCollection(3).Select
ActiveChart.SetElement (msoElementSecondaryCategoryAxisShow)
ActiveChart.SetElement (msoElementLegendNone)
ActiveChart.Axes(xlCategory, xlSecondary).Select
ActiveChart.Axes(xlCategory, xlSecondary).ReversePlotOrder = True
ActiveChart.Axes(xlCategory, xlSecondary).Crosses = xlAutomatic
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).Crosses = xlMaximum
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.Axes(xlValue, xlSecondary).Crosses = xlAutomatic
Selection.MajorTickMark = xlInside
Selection.MajorTickMark = xlNone
Selection.TickLabelPosition = xlNone
ActiveChart.Axes(xlCategory).Select
Selection.MajorTickMark = xlNone
Selection.TickLabelPosition = xlNone
ActiveChart.SeriesCollection(1).Select
Selection.Format.Fill.Visible = msoFalse
Selection.Format.Line.Visible = msoFalse
ActiveChart.ChartArea.Select
Application.CutCopyMode = False
ActiveChart.Location Where:=xlLocationAsNewSheet

Thanks in advance! Karen