Two Worksheet_Change on same Worksheet

[happyhungarian]

Hi, I have two different change events I'd like to run on the same sheet. I'm having trouble having the second one run. Here's the first change:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C3")) Is Nothing Then Exit Sub

Application.CutCopyMode = False
ActiveWorkbook.RefreshAll
DoEvents

Sheets("Pivots").PivotTables("PivotTable1").PivotCache.Refresh
DoEvents

MsgBox "Queries Complete"

End Sub


And here's the second:

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub



Dim pt As PivotTable
Dim FieldContract_Date As PivotField
Dim NewContract_Date As String




Set pt = Worksheets("Pivots").PivotTables("PivotTable2")
Set FieldContract_Date = pt.PivotFields("Contract_Date")
NewContract_Date = Worksheets("Worksheet").Range("C5").Value

pt.RefreshTable


With pt
FieldContract_Date.ClearAllFilters
FieldContract_Date.CurrentPage = NewContract_Date
pt.RefreshTable

End With

End Sub

 

[dreid1011]

My first thought is that you need to put the second code within the first Worksheet_Change event.

Like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C3")) Is Nothing Then 
Exit Sub
Else
    Application.CutCopyMode = False
    ActiveWorkbook.RefreshAll
    DoEvents

    Sheets("Pivots").PivotTables("PivotTable1").PivotCache.Refresh
    DoEvents

    MsgBox "Queries Complete"
End If

If Intersect(Target, Range("C5")) Is Nothing Then 
Exit Sub
Else
Dim pt As PivotTable
Dim FieldContract_Date As PivotField
Dim NewContract_Date As String




Set pt = Worksheets("Pivots").PivotTables("PivotTable2")
Set FieldContract_Date = pt.PivotFields("Contract_Date")
NewContract_Date = Worksheets("Worksheet").Range("C5").Value

pt.RefreshTable


With pt
FieldContract_Date.ClearAllFilters
FieldContract_Date.CurrentPage = NewContract_Date
pt.RefreshTable

End With
End If
End Sub

Your IF statements checking which cell is Target will determine which part of the code is run.

 

[happyhungarian]

The second Worksheet_Change isn't triggering. Is it become of the first If Intersect(Target, Range("C3")) Is Nothing Then Exit Sub? Since C3 isn't changing the macro stops?

 

[Eric W]

You can only have one Worksheet_Change event per sheet, but you can combine multiple functions within one macro:

Private Sub Worksheet_Change(ByVal Target As Range)

' If C3 is changed, execute this
    If Not Intersect(Target, Range("C3")) Is Nothing Then

        Application.CutCopyMode = False
        ActiveWorkbook.RefreshAll
        DoEvents

        Sheets("Pivots").PivotTables("PivotTable1").PivotCache.Refresh
        DoEvents

        MsgBox "Queries Complete"
    End If

' If C5 is changed, execute this
    If Not Intersect(Target, Range("C5")) Is Nothing Then

        Dim pt As PivotTable
        Dim FieldContract_Date As PivotField
        Dim NewContract_Date As String

        Set pt = Worksheets("Pivots").PivotTables("PivotTable2")
        Set FieldContract_Date = pt.PivotFields("Contract_Date")
        NewContract_Date = Worksheets("Worksheet").Range("C5").Value

        pt.RefreshTable

        With pt
            FieldContract_Date.ClearAllFilters
            FieldContract_Date.CurrentPage = NewContract_Date
            pt.RefreshTable
        End With
    
    End If

End Sub

dreid1011, you have the basic concept correct, but if I'm reading your code correctly, then if C5 is changed, the second part of your macro will never be executed.

 

[happyhungarian]

Excellent! That did the trick. Thank you to you both.

 

[dreid1011]

dreid1011, you have the basic concept correct, but if I'm reading your code correctly, then if C5 is changed, the second part of your macro will never be executed.

Yes, I realized that now. I wasn't paying much attention to the 'Exit Sub' lines that well. I seem to overlook small details like that

 

[Eric W]

Glad we could help!