No VBA Recorded in Excel 2007

amthorpe

New Member
Joined
Aug 29, 2009
Messages
3
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.

Code:
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

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


Help :confused:
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to the Board.

Recorded in Excel 2000:

Code:
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

New Member
Joined
Aug 29, 2009
Messages
3
Thanks VoG, thought it might be a failing of Microsoft.

Thanks Andrew however
Code:
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.

:mad:
 

amthorpe

New Member
Joined
Aug 29, 2009
Messages
3
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.
 

Forum statistics

Threads
1,148,108
Messages
5,744,878
Members
423,908
Latest member
Getfour

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top