No VBA Recorded in Excel 2007

[amthorpe]

Why when I record a marco and change a chart (I have over a 100 and the migration from 2000 to 2007 loses the lines due to the formatting used) does the VBA no show what I am doing ? e.g.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Points(3).Select
ActiveSheet.ChartObjects("Chart 1").Activate

In the example below I changed the fill colour and border style and colour of Data Point 3 yet the VBA only shows I selected Data Point 3.

In the following e.g I changed the Chart Type and the colour of all the charted Data

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).ChartType = xlCylinderCol
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 1").Activate

Help

 

[VoG]

The charting engine was changed in XL 2007 and the macro recorder does not record all actions (in fact it records very few). This appears to have been fixed in XL 2010.

 

[Andrew Poulsom]

Welcome to the Board.

Recorded in Excel 2000:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 07/07/2010 by apoulsom
'
'
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(3).Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlThin
        .LineStyle = xlDash
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlAutomatic
        .MarkerForegroundColorIndex = 3
        .MarkerStyle = xlDiamond
        .MarkerSize = 5
        .Shadow = False
    End With
    ActiveChart.PlotArea.Select
End Sub

Hope it helps.

 

[amthorpe]

Thanks VoG, thought it might be a failing of Microsoft.

Thanks Andrew however

Recorded in Excel 2000

Doesn't really help much, I have one machine with 2007 company won't lend me a machine with 2000 or let me install it so I can't do the updates in 2000. The 2000 and even the 2003 code is so vastly different that it won't run in 2007, kept falling over when I did what you did.



Microsoft, first Application.Filesearch goes, so 10 lines become 30 then they fail to include the correct capabilities with VBA, you'd have thought if they have fixed it for 2010, they would issue an update for 2007.

 

[Andrew Poulsom]

In what way didn't my code work in Excel 2007?

 

[amthorpe]

Well, actually it did (I assumed it wouldn't as I had recorded an update in 2000 to try on a 2007 machine after someone else was upgraded), however the problem I have is that some of my lines are shaded with colour and pattern (there are quite a few on th chart), Pattern is no longer an option in 2007, so when you try to change the line and want to bring it up to the 2007 visual levels, it is almost impossibe as I cannot find anywhere a full list of the VBA code for charting in 2007, have the full set of John Walkenbach's books which I have found to be the most helpful, but can't find it in them or online.

Sorry for dismissing your code, it appears it does work, just doesn't help with updating complex charts to use 2007 to it's best ability.