Solved: Chart-Moving Fails Sometime...Why??

[Cyberdude]

I use the basic logic shown below to automatically move a chart down as data is added to a nearby column. I also use it to manually control the vertical position of the chart by clicking a button. Today it starting failing sometimes, and I can't figure out why. I have similar logic for horizontal position in another macro. I discovered that by executing the horizontal logic, then executing the vertical logic it often would work OK. But usually when I execute it by call from another macro or by clicking the button it fails with:
"Select method of ChartArea class failed".
The fact that it works fine sometimes is puzzeling. What's wrong with this logic?
Sub MoveChartVertTest() '10/3/05
Dim Msg$
Worksheets("Chart").Select
On Error GoTo ErrMsg
ActiveSheet.ChartObjects("Chart 1").Select
ActiveChart.ChartArea.Select '<--(Run-time Error)
ActiveSheet.Shapes("Chart 1").IncrementTop -15.6
On Error GoTo 0
Exit Sub
ErrMsg:
Msg = "Error '" & Err & "' occurred in macro 'MoveChartVert''" & vbCr & _
"""Select method of ChartArea class failed""" & vbCr & vbCr & _
"ChartObjects number 'Chart 1' was specified."
On Error GoTo 0
MsgBox Msg, vbCritical
End Sub 'MoveChartVert'

[chocobochick]

I suspect this belongs in Excel Help, for which I'm not as experienced I'm afraid.

[Cyberdude]

Oops ... too quick on the keyboard and I undershot. Sorry 'bout that. Is there a way to have this post transferred to the Excel clan?

[Jacob Hilderbrand]

Moved.

Is there a Chart Area? If you have a chart w/o a source you will get a runtime error if you try to select the ChartArea since it doesn't exist.

Maybe you can attach the workbook that has a chart that is failing for you and we can take a closer look.

[BlueCactus]

What error message do you get if you get rid of the error trapping?

Does it get any more reliable if you use
ActiveSheet.ChartObjects("Chart 1").Chart.ChartArea.Select?

[Cyberdude]

To BlueCactus:
You asked what message I get if I don't trap the error. As I said in my first post, I get:
"Select method of ChartArea class failed".

To drj, keep in mind that it DOES work at times with no code modifications. It will be a little difficult to send a copy of the workbook. I'll see what I can do.

Do you see anything wrong with the code as I have written it?

[BlueCactus]

To BlueCactus:
You asked what message I get if I don't trap the error. As I said in my first post, I get:
"Select method of ChartArea class failed".

Yeah, but that error message is manufactured by your VBA code in the error-trapping routine. I want to know what the underlying message is.

[Andy Pope]

Hi,

I think your code will work if the chart is already selected.
Try the activate method to over come the problem.

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select

Or better yet don't select the chart at all.
ActiveSheet.Shapes("Chart 1").IncrementTop -15.6

[Cyberdude]

To BlueC: I think you misunderstood what you saw. The error message in my error handler IS the message I get when I disable the error handling. I didn't want to forget what it said, so I put it in the error handler.
To Andy: Just a few minutes ago I was comparing the problem macro with another one like it in a different workbook, and I DID change Select to Activate. That apparently was the problem because now it's working OK. I probably had used Activate originally, then for reasons unknown, I must have changed it to Select.
Thanx guys, I appreciate the suggestions.

[BlueCactus]

Yeah, I misunderstood you - sorry.

I think that your problem was that ChartArea is a child of Chart, not ChartObject. When you ChartObject.Activate, you automatically ChartObject.Chart.Activate, but when you ChartObject.Select, it probably does not carry to ChartObject.Chart.