Using Worksheet_Change for two Different Target.Address (Excel 2007)

[blixamarkham]

Greetings. I have two cells that are both drop down lists referenced from a Vlookup Table. When One cell is "D11" for the "Group" and the other is "D12" for the "Campaign". When the user selects from the drop down cell what they want, I have 3 pivot tables that should change their filter based on the drop down selected. My code works fine for a single cell but when I tried to extend it to two different cells it won't run. I have done research and know I probably have to use Intersect somewhere but that is about as far as I have gotten. The excel file is over 50MB so posting the file will not be feasible. Anyone who helps, thank you for your time. Here is the code:

My working code for 1 cell:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = Range("D13").Address Then Exit Sub
    Dim pt As PivotTable
    Dim ptItem As PivotItem
    
    On Error GoTo CleanUp
    Application.EnableEvents = False
    
    For Each pt In Worksheets("ClosedLoansAll").PivotTables
        
        With pt.PivotFields("Campaign")
            If .EnableMultiplePageItems = True Then
                .ClearAllFilters
            End If
            If LCase$(Target.Value) = "all" Then
               .ClearAllFilters
                   Else
               Set ptItem = .PivotItems(Target.Value)
               If Not ptItem Is Nothing Then
                   .CurrentPage = Target.Value
               End If
            End If
        End With
    Next
CleanUp:
   Application.EnableEvents = True
   
End Sub

My attempt at extending my code to two different cells.

Private Sub Worksheet_Change(ByVal Target As Range)
'    If Not Target.Address = Range("D12").Address Then Exit Sub
  '  If Not Target.Address = Range("D11").Address Then Exit Sub


    Dim pt As PivotTable
    Dim ptItem As PivotItem
   
    On Error GoTo CleanUp
    Application.EnableEvents = False
   
    For Each pt In Worksheets("ClosedLoansByBranch").PivotTables
        Target.Address = Range("D12").Address
        If Not Intersect(Target, Range("D12")) Is Nothing Then
        With pt.PivotFields("Campaign")
       '     If Not Target.Address = Range("D12").Address Then Exit Sub
                If .EnableMultiplePageItems = True Then
                    .ClearAllFilters
                End If
                If LCase$(Target.Value) = "all" Then
                    .ClearAllFilters
                Else
                Set ptItem = .PivotItems(Target.Value)
                    If Not ptItem Is Nothing Then
                        .CurrentPage = Target.Value
                    End If
                End If
        End With
        Else
        With pt.PivotFields("Group")
    '          If Not Target.Address = Range("D11").Address Then Exit Sub
            If .EnableMultiplePageItems = True Then
                .ClearAllFilters
            End If
            If LCase$(Target.Value) = "all" Then
               .ClearAllFilters
                   Else
               Set ptItem = .PivotItems(Target.Value)
               If Not ptItem Is Nothing Then
                   .CurrentPage = Target.Value
               End If
            End If
        End With
        End If
    Next
CleanUp:
   Application.EnableEvents = True
   
End Sub

 

[V_Malkoti]

Either of these two should work:

If Target.Address <> Range("D11").Address And Target.Address <> Range("D12").Address Then Exit Sub

If Intersect(Target, Range("D11:D12")) Is Nothing Then Exit Sub

 

[blixamarkham]

I made the change you suggested however, when I select from the drop down list, none of the pivot table filters update to match. There is no error however. It seems like the code is not executing. Could it have to do with the Target.Address or Worksheet_Change?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> Range("D11").Address And Target.Address <> Range("D12").Address Then Exit Sub
    
    Dim pt As PivotTable
    Dim ptItem As PivotItem
   
    On Error GoTo CleanUp
    Application.EnableEvents = False
   
    For Each pt In Worksheets("ClosedLoansByBranch").PivotTables
        With pt.PivotFields("Campaign")
                If .EnableMultiplePageItems = True Then
                    .ClearAllFilters
                End If
                If LCase$(Target.Value) = "all" Then
                    .ClearAllFilters
                Else
                Set ptItem = .PivotItems(Target.Value)
                    If Not ptItem Is Nothing Then
                        .CurrentPage = Target.Value
                    End If
                End If
        End With
        With pt.PivotFields("Group")
             If .EnableMultiplePageItems = True Then
                .ClearAllFilters
            End If
            If LCase$(Target.Value) = "all" Then
               .ClearAllFilters
                   Else
               Set ptItem = .PivotItems(Target.Value)
               If Not ptItem Is Nothing Then
                   .CurrentPage = Target.Value
               End If
            End If
        End With
    Next
CleanUp:
   Application.EnableEvents = True
   
   
End Sub

 

[V_Malkoti]

That If statement causes code execution to exit Worksheet_Change subroutine if the updated cell is neither D11 or D12. Was that what you intended to check before proceeding?
If no, then can you tell what should be the condition?
If yes, then can you place a breakpoint at that If statement and then try to update the sheet and see what is happening in the code?

 

[blixamarkham]

I am sorry if I am being difficult to understand. I took a screenshot to try and give a better understanding of what I want. As for the code, my intention was when one of those cells are changed, the code executes for the appropriate pivot field change. Thank you for your help.

 

[V_Malkoti]

Looks like If statement is correct. Did you try placing a breakpoint in the code and then update D11 or D12 to follow the execution of code?

Is your application.enableevents set to true or false? Can you set it from Immediate window and then try to update the sheet?

Also you might need to place an If statement before each With pt.PivotFields() statement. Else you will be applying value of changed cell to both filters.

 

[blixamarkham]

Application.enableevents is set to true. When I put the breakpoint in and go step by step everything goes fine until the code hits:

Set ptItem = .PivotItems(Target.Value)

Once it hits that line it jumps all the way to

Application.EnableEvents = True

 

[V_Malkoti]

This means that the value in Target cell is not found in the listed values of that PivotField, which is creating an error. So you need to place If statements to detect Target for right PivotField.

Try this change:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' If Target.Address <> Range("D11").Address And Target.Address <> Range("D12").Address Then Exit Sub
    
    Dim pt As PivotTable
    Dim ptItem As PivotItem
   
    On Error GoTo CleanUp
    Application.EnableEvents = False
   
    For Each pt In Worksheets("ClosedLoansByBranch").PivotTables
[COLOR=#0000ff]        If Target.Address = Range("D12").Address Then[/COLOR]
            With pt.PivotFields("Campaign")
                    If .EnableMultiplePageItems = True Then
                        .ClearAllFilters
                    End If
                    If LCase$(Target.Value) = "all" Then
                        .ClearAllFilters
                    Else
                    Set ptItem = .PivotItems(Target.Value)
                        If Not ptItem Is Nothing Then
                            .CurrentPage = Target.Value
                        End If
                    End If
            End With
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]        If Target.Address = Range("D11").Address Then[/COLOR]
            With pt.PivotFields("Group")
                 If .EnableMultiplePageItems = True Then
                    .ClearAllFilters
                End If
                If LCase$(Target.Value) = "all" Then
                   .ClearAllFilters
                       Else
                   Set ptItem = .PivotItems(Target.Value)
                   If Not ptItem Is Nothing Then
                       .CurrentPage = Target.Value
                   End If
                End If
            End With
[COLOR=#0000ff]        End If[/COLOR]
    Next
CleanUp:
   Application.EnableEvents = True
   
   
End Sub

 

[blixamarkham]

That did it! Thank you so much for your help! Have a great day!

 

[V_Malkoti]

Glad I could help.