"Compile Error: ambiguous name detected: Worksheet_Change" notification

Question

I retrieved a helpful VBA command from the internet which allows me to change an arbitrary cell in Excel which acts as a filter for my pivot table. When I try to reuse this same command on the same sheet but for a different filter, I get a "Compile Error: ambiguous name detected: Worksheet_Change" notification and the code stops working... I tried to change "End Sub" to "Exit Sub" which did not work, and I am hoping someone can point me in the right direction here.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = Range("D2").Address Then Exit Sub
    Dim PT As PivotTable
    Dim ptItem As PivotItem
    On Error Resume Next
    For Each PT In Worksheets("Tier Comps").PivotTables
        With PT.PivotFields("Product")
            If .EnableMultiplePageItems = True Then
                .ClearAllFilters
            End If
            Set ptItem = .PivotItems(Target.Value)
            If Not ptItem Is Nothing Then
                .CurrentPage = Target.Value
            End If
        End With
    Next
Exit Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = Range("D3").Address Then Exit Sub
    Dim PT As PivotTable
    Dim ptItem As PivotItem
    On Error Resume Next
    For Each PT In Worksheets("Tier Comps").PivotTables
        With PT.PivotFields("Country Tier")
            If .EnableMultiplePageItems = True Then
                .ClearAllFilters
            End If
            Set ptItem = .PivotItems(Target.Value)
            If Not ptItem Is Nothing Then
                .CurrentPage = Target.Value
            End If
        End With
    Next
End Sub

Hoping to have two separate cells modify two separate filters for one pivot table on the same sheet.

You have two subs with the same name `Private Sub Worksheet_Change(ByVal Target As Range)` You can't have that — Siddharth Rout, Sep 13 '19 at 09:12
I had a similar issue previously, oddly there is a very simple solution - in VBA you can just specify that the cell that has the pivot table filter be the value that you want, and it automatically updates the table; i.e. literally name the range and say what value. — Plato77, Sep 13 '19 at 09:13
Chris, if you want to handle both cells i.e D2 and D3 then you can do that in one `Worksheet_Change` event. You do not need two separate subs and that too with the same name for this :) — Siddharth Rout, Sep 13 '19 at 09:14
@SiddharthRout, and may I ask - how do I do this? When I say rookie, I mean I have no VBA knowledge... just search Google — Chris Gonzalo Ugarte, Sep 13 '19 at 09:16
See [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) You can use `If Not Intersect(Target, Range("D2")) Is Nothing Then` as shown in that link and in the `ElseIf` do the same for D3 :) Give it a try. If you get stuck, post back. — Siddharth Rout, Sep 13 '19 at 09:20

score 0 · Answer 1 · edited Dec 01 '21 at 09:39

Please take a full read through --> https://stackoverflow.com/help/minimal-reproducible-example

One of the best parts about working through the process of making an MCVE is a lot of the time during the process of writing the question you will solve it yourself as you strip out more code to find the issue.

Here is a simplified answer, modify the MsgBox portion with your actual code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D2")) Is Nothing Then
       MsgBox "Cell D2 Change Event"
    End If
    If Not Intersect(Target, Range("D3")) Is Nothing Then
       MsgBox "Cell D3 Change Event"
    End If
Exit Sub

Also, just for completeness sake. The issue is you can't have two subs with the same name. Another issue to look out for that I ran into is don't name Modules the same name as the sub. I usually add an _. Eg Module = Test_Sub and then Private Sub TestSub()

"Compile Error: ambiguous name detected: Worksheet_Change" notification

1 Answers1