Typemismatch when duplicating graph
 

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

Typemismatch when duplicating graph
 

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"PBradac" wrote in message
...
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

Typemismatch when duplicating graph
 

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

On Tue, 2 Feb 2010 13:03:23 -0000, "Andy Pope"
wrote:

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

Typemismatch when duplicating graph
 

My bad, I left of the Count property.

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

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"PBradac" wrote in message
...
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

On Tue, 2 Feb 2010 13:03:23 -0000, "Andy Pope"
wrote:

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

Typemismatch when duplicating graph
 

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

On Wed, 3 Feb 2010 09:40:35 -0000, "Andy Pope"
wrote:

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

Typemismatch when duplicating graph
 

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"PBradac" wrote in message
...
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

On Wed, 3 Feb 2010 09:40:35 -0000, "Andy Pope"
wrote:

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

Typemismatch when duplicating graph
 

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

On Wed, 3 Feb 2010 12:44:22 -0000, "Andy Pope"
wrote:

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