+ Reply to Thread
Results 1 to 2 of 2

Stop code from triggering worksheet event

Hybrid View

  1. #1
    Registered User
    Join Date
    06-18-2007
    MS-Off Ver
    2007
    Posts
    77

    Stop code from triggering worksheet event

    I am attempting to use the below code, but when using the EnableEvents, the three lines of pivot tables do not update. If I remove the EnableEvents, then it enters a never ending loop. I need for when any one of the pivot tables is updated/changed, for the others to automatically update as well, then the rest of the code runs. Thanks for any help.

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    
        Dim DAO As String
        DAO = Range("F12").Value
    
        'reset the formatting for the pivot tables on the Options tab after a refresh
        
        If (Target.Name = "DataAsOf") Or (Target.Name = "ThisYearPeriod") Or (Target.Name = "LastYearPeriod") Then
    
        Application.EnableEvents = False
        Worksheets("Options").PivotTables("DataAsOf").RefreshTable
        Worksheets("Options").PivotTables("ThisYearPeriod").RefreshTable
        Worksheets("Options").PivotTables("LastYearPeriod").RefreshTable
        Application.EnableEvents = True
        
            Range("E10:F10").Copy
            Range("E12:F12,E32:F32,E52:F52").PasteSpecial Paste:=xlPasteFormats
            Worksheets("Options").Select
            Range("E76").Select
            Application.CutCopyMode = False
    
        End If
    Last edited by mashley; 09-17-2010 at 02:06 PM.

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: Stop code from triggering worksheet event

    I can't see anything there that would prevent the tables from updating.
    Everyone who confuses correlation and causation ends up dead.

+ Reply to Thread

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