Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup without duplicating | Excel Programming | |||
De-duplicating | Excel Discussion (Misc queries) | |||
duplicating a spreadsheet | New Users to Excel | |||
Duplicating a Workbook | Excel Programming | |||
duplicating macros | Excel Programming |