Typemismatch when duplicating graph

[PBradac]

Long time ago I have created a VBA code in which I create several
graphs from data in worksheets. It was developed and is still working
in Excel 2003.

Now with Excel 2007 and Excel 2010 Beta I get

Run-time error '13':
Type mismatch

when program control reaches the line:

Set graf = list.ChartObjects(1).Duplicate

The variables are explicitly declared with:

Dim list As Worksheet, graf As ChartObject

Any ideas how to solve the problem?

TIA, Primoz

 

[Andy Pope]

Hi,

Looks like a bug. Try referencing the latest chart object.

Dim list As Worksheet, graf As ChartObject

list.ChartObjects(1).Duplicate
Set graf = list.ChartObjects(list.ChartObjects)

Cheers
Andy

 

[PBradac]

Thank you Andy for your prompt answer. I was not so quick though...

I tried your suggestion but it didn't work. I got the error:

Run-time Error '1004':
Method 'ChartObjects' of object '_Worksheet' failed

I admit I don't understand the setting of the graf variable quite
well. Doesn't list.ChartObject() require an index and
list.ChartObjects is a set, isn't it?

In any case, what is to be done? I can't believe I'm the first one in
the world who encountered this bug. Can the people at Microsoft
somehow be alerted of the problem?

Cheers,
Primoz

 

[Andy Pope]

My bad, I left of the Count property.

Set graf = list.ChartObjects(list.ChartObjects.Count)

Cheers
Andy

 

[PBradac]

Whooooaa, Andy you solved the problem! I get the duplicated chart on
the workshhet.

But my joy is only partial. Later on in the code I crush in another
wall:

When executing:
ActiveChart.TextBoxes(1).Formula = wk
' BTW I construct wk giving something like "Sheet1!R2C3"

Excel says:
Run-time error '1004':
Unable to set the Formula property of the TextBox class.

As far as I can see Excel is even right as I can't find in its
Help that ActiveSheet would have TextBox property and this in turn
Format property. Googling didn't enlighten me very much. I only saw
examples... As I'm just a casual Excel VBA programmer I can't for the
life of me remember how I got to this line (more than 7 years ago). I
presumably recorded a macro and transferred it to my code. These
properties are not even to be found in Excel 2003 VBA object model
either (I tried Object Browser). If it's not too much to ask, could
you give me a hint how to proceed. I'm sure it'll be no brainer for
you!

Thank you very much in advance.

Regards,
Primoz

 

[Andy Pope]

Worked for me using A1 notation.

ActiveChart.TextBoxes(1).Formula = "Sheet1!C2"

You can use Application.ReferenceStyle to check which style to use.

Cheers
Andy

 

[PBradac]

Andy, I hardwired into the formula the contents in the form you
suggested to verify if "I can get through". And it worked!

I think I'll be able to change the whole code to be generally
executable again.

Thank you again for your *first class* support.

Cheers,
Primoz