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:

 Private Sub Chart_Activate()

Sheets("Pivot Table").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Charts("Chart1").Select

End Sub

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:

If Cells(i, 1) = "Whatever" then

....etc etc

Any ideas are really appreciated.

Thanks!,

Brian