creating and formating charts in vba

[mbababrik]

i want to write a code that generates data and add a chart.
i tried recording a macro (subroutine) but i read that excl2007 has problems.
so the x values go from 1-100 on column A and y=(2*x)+7 on column B.
then from this data i want to add an xy scatter with smooth lines. then i want to format the chart: delete gridlines, add axes titles and chart title (font size 16), make the data series line black, format axes x and y to where the axis line color is black and 1.5 in width and the font size of axes numbers is 16. delete the legend.
when i tried recording i got this
code:
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveCell.FormulaR1C1 = "x"
Range("B1").Select
ActiveCell.FormulaR1C1 = "y"
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "2"
Range("A2:A3").Select
Selection.AutoFill Destination:=Range("A2:A101"), Type:=xlFillDefault
Range("B2").Select
ActiveCell.FormulaR1C1 = "=2*RC[-1]+7"
Range("B2").Select
Selection.Copy
Range("B3:B101").Select
ActiveSheet.Paste
Range("A2:B101").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$2:$B$101")
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
End Sub
this gives me two data series on the chart and when i try formatting the axes and chart it just doesnt record it.
thank you for your help

 

[Weaver]

So you're using 2007 and you did all those formatting activities while the macro recorder was on and it didn't record them?

I had a go and it recorded some of the actions but not others, curiously not the axis font mods.

This code should do some of the things you're after - I'd have to fire up my ol machine (currently without some major components) and play around with a copy of 2003 to answer all of this one.

    With ActiveChart
        .PlotArea.ClearFormats
        .Axes(xlValue).MajorGridlines.Delete
        .Axes(xlValue).TickLabels.Font.Size = 16
        .Axes(xlCategory).TickLabels.Font.Size = 16
    End With

As to why these steps no longer record, well who knows?

HTH