Caught in an Infinite Loop

[mgri]

Hi All,

I'm hoping one of you excel gurus will be able to help me with a problem I'm experiencing.

I want to update an excel chart that is based on data in a pivot table (which in turn is based on another sheet with raw data). I've written a macro that runs automatically when the chart is selected. This macro updates the pivot table on the other sheet and in doing so the chart updates itself automatically.

The problem is that at the end of the macro I've written I need to select the chart sheet again in order to leave the now updated chart visible, and doing that runs the pivot chart macro again because the macro runs automatically when the chart is updated. I need to select the chart WITHOUT running the automatic macro the second time around. Grrr! The simple code below should make this problem clear:

Please Login or Register  to view this content.

Do we have the technology to get around this?

Incidently, the reason I have the chart arranged on a different sheet is (A) because it's neater and (B) because when I have the graph on the same sheet as the pivot table I was getting this error:

Run-time error '1004', Method 'Cells' of Object'_Global' failed

when I tried this code:

Please Login or Register  to view this content.

....etc etc

Any ideas are really appreciated.

Thanks!,

Brian

[shg]

Do we have the technology to get around this?

Yes we do. Would you please wrap your code with code tags?

[mgri]

Hi All,

I'm hoping one of you excel gurus will be able to help me with a problem I'm experiencing.

I want to update an excel chart that is based on data in a pivot table (which in turn is based on another sheet with raw data). I've written a macro that runs automatically when the chart is selected. This macro updates the pivot table on the other sheet and in doing so the chart updates itself automatically.

The problem is that at the end of the macro I've written I need to select the chart sheet again in order to leave the now updated chart visible, and doing that runs the pivot chart macro again because the macro runs automatically when the chart is updated. I need to select the chart WITHOUT running the automatic macro the second time around. Grrr! The simple code below should make this problem clear:

Please Login or Register  to view this content.

Do we have the technology to get around this?

Incidently, the reason I have the chart arranged on a different sheet is (A) because it's neater and (B) because when I have the graph on the same sheet as the pivot table I was getting this error:

Run-time error '1004', Method 'Cells' of Object'_Global' failed

when I tried this code:

Please Login or Register  to view this content.

Any ideas are really appreciated.

Thanks!,

Brian

[shg]

Try this:

Please Login or Register  to view this content.

[mgri]

You are a gentleman and a scholar! Thanks very much!

Brian