Search code examples
excelexcel-2007excel-2002vba

VBA Excel 2007 macro dies in Excel 2002

I have just tried to send a workbook to a friend that has a chart with a drop-down box on it. In excel 2007 I have used:

If Chart2.Shapes(2).ControlFormat.ListCount = "16" Then

To check the size of the list so that it doesnt get entered in again (resulting in an extremely long list that repeats itself). This line works well in Excel 2007, but gives a Object doesn't support this property or method. error in Excel 2002.

What is the difference between working with shapes in 2002 vs 2007?

Solution

  • Ok, it ended up being something simple that took me ages to pick up on.

    Excel 2007 counts shapes differently to 2002. The shape I was trying to reference in 2007 (2) was now (1) in 2002.

    So, the final result was of course

    If Chart2.Shapes(1).ControlFormat.ListCount = "16" Then