Results 1 to 5 of 5

Caught in an Infinite Loop

Threaded View

  1. #1
    Registered User
    Join Date
    11-02-2007
    Posts
    5

    Unhappy Caught in an Infinite Loop

    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
    Last edited by VBA Noob; 11-02-2007 at 04:34 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1