Problems with ChartObjects

[pwermuth]

Can somebody help me fix the following:

When I run the following line of code:

Worksheets(destinationSheet).ChartObjects(chartToCopy).Activate

where destinationSheet is a string with the name of the sheet "Output 2", and chartToCopy is also a string with the name of a chart "char 11"

I get a "Run-time error '1004':
Unable to get the ChartObjects property of the Worksheet class."


I think it worked when I used Activesheet.ChartObjects(chartToCopy).Activate but I do not want to make it dependent on being on that sheet.

[Rob Bovey]

"pwermuth" <[email protected]> wrote in
message news:[email protected]...
> When I run the following line of code:
> Worksheets(destinationSheet).ChartObjects(chartToCopy).Activate
> where destinationSheet is a string with the name of the sheet "Output
> 2", and chartToCopy is also a string with the name of a chart "char 11"
> I get a "Run-time error '1004':
> Unable to get the ChartObjects property of the Worksheet class."

My guess is you have an incorrect name in either your destinationSheet
or chartToCopy variable. Try hard-coding the names directly into the line of
code above and see if it works properly that way. If so, one variable or the
other has the wrong name in it (or if there are multiple worksheets with
chart objects on them you may have the name of a worksheet with the name of
a chart object that's located on a different worksheet).

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

[K Dales]

Your syntax is OK and it should work - it works for me running Excel 2002 on
Win XP. The most likely problem is that you do not have the correct names in
your variables (probably the chart name). I know you say the names are
correct but I don't see what else would be causing this to fail with the
error message you are getting. When you get the error go into debug mode and
check all the variables and the actual names, either through the locals
window or the immediate pane. And watch for extra spaces in any of the names
- that is often a problem that is hard to spot at first.

--
- K Dales


"pwermuth" wrote:

>
> Can somebody help me fix the following:
>
> When I run the following line of code:
>
> Worksheets(destinationSheet).ChartObjects(chartToCopy).Activate
>
> where destinationSheet is a string with the name of the sheet "Output
> 2", and chartToCopy is also a string with the name of a chart "char 11"
>
> I get a "Run-time error '1004':
> Unable to get the ChartObjects property of the Worksheet class."
>
>
> I think it worked when I used
> Activesheet.ChartObjects(chartToCopy).Activate but I do not want to
> make it dependent on being on that sheet.
>
>
> --
> pwermuth
> ------------------------------------------------------------------------
> pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
> View this thread: http://www.excelforum.com/showthread...hreadid=386289
>
>

[Greg Wilson]

The only way I can replicate your problem is by spelling the name of the
chartobject wrong - i.e. if the ChartToCopy variable passes an incorrect
spelling you'll get this message. If the worksheet name is misspelled as
passed by the destinationSheet variable then I get the "subscript out of
range" message instead. Otherwise, your code works for me.

Regards,
Greg

"pwermuth" wrote:

>
> Can somebody help me fix the following:
>
> When I run the following line of code:
>
> Worksheets(destinationSheet).ChartObjects(chartToCopy).Activate
>
> where destinationSheet is a string with the name of the sheet "Output
> 2", and chartToCopy is also a string with the name of a chart "char 11"
>
> I get a "Run-time error '1004':
> Unable to get the ChartObjects property of the Worksheet class."
>
>
> I think it worked when I used
> Activesheet.ChartObjects(chartToCopy).Activate but I do not want to
> make it dependent on being on that sheet.
>
>
> --
> pwermuth
> ------------------------------------------------------------------------
> pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
> View this thread: http://www.excelforum.com/showthread...hreadid=386289
>
>

[pwermuth]

Thank you for your responses.

I will try hardcoding the name.

How can I check what the value of a variable is once I am in debug mode? I am completely new to VB.

[Rob Bovey]

"pwermuth" <[email protected]> wrote in
message news:[email protected]...
> Thank you for your responses.
> I will try hardcoding the name.
> How can I check what the value of a variable is once I am in debug
> mode? I am completely new to VB.

There are several ways to do this. For simple variables, once you are in
break mode all you should need to do is hover your mouse cursor over the
variable whose value you want to see and a yellow popup window will appear
that displays its current value.

The most robust way to see the value of a variable is to select it with
your mouse, right-click and choose Add Watch from the menu. Click OK in the
resulting Add Watch dialog and the Watch window will open with your variable
displayed in it, along with its value, data type and watch context.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm