If Not Intersect Target and Pivot Table

[olympiac]

My code:

If Not Intersect Target and Pivot Table
Private Sub Worksheet_Change(ByVal Target As Range)
Dim PT1 As PivotTable
If Not Intersect(Target, Range("B7")) Is Nothing Then
Set PT1 = Sheets("PIVOT").PivotTables("MainPivotTable")
PT1.PivotFields("Team").CurrentPage = Sheets("Summary").Range("B7").Value
Set PT1 = Nothing
End If


What does the code do?
The value chosen from the list in Cell B7 updates the Page field of the Pivot Table "MainPivotTable" and consequently a table gets the data updated (getpivot data)

My query
What about If I choose a value from the list in Cell B7 that does not exist in the pivot table? Is there a way to get a message box saying "value doesn't exist"?

 

[Jerry Sullivan]

What about If I choose a value from the list in Cell B7 that does not exist in the pivot table? Is there a way to get a message box saying "value doesn't exist"?

Hi Olympiac,

Yes, you can test to see if the value doesn't exist and report that in a message box. In fact, you can extend this to any of the other object names that might not match.

This would be overkill in most cases, but will give you an idea of how to test for a few different types of potential errors.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B7")) Is Nothing Then
        Dim strSheetName As String: strSheetName = "PIVOT"
        Const conPTName As String = "MainPivotTable"
        Const conPTFieldName As String = "Team"
        Dim strPageName As String
        Dim PT1 As PivotTable, pvtField1  As PivotField
        On Error Resume Next
 
       '---Test if worksheet "PIVOT" exists
        If UCase(strSheetName) <> UCase(Sheets(strSheetName).Name) Then
            MsgBox "Worksheet: " & strSheetName & " not found"
            Exit Sub
        End If
 
        '---Test if Pivot Table "PivotTable1" exists
        Set PT1 = Sheets(strSheetName).PivotTables(conPTName)
        If PT1 Is Nothing Then
            MsgBox "Pivot Table: " & conPTName & " not found"
            Exit Sub
        End If
 
        '---Test if PivotField "Team" exists
        Set pvtField1 = PT1.PivotFields(conPTFieldName)
        If pvtField1 Is Nothing Then
            MsgBox "PivotField: " & conPTFieldName & " not found"
            Exit Sub
        End If
 
        '---Try to set value in Cell B7 as Current Page
        strPageName = Sheets("Summary").Range("B7").value
        With pvtField1
            .ClearAllFilters
            .CurrentPage = strPageName
        End With
 
        '---Report if PageName Item in Cell B7 exists in Team Field
        If pvtField1.CurrentPage <> strPageName Then
           MsgBox "Page item: " & strPageName & " not found"
             Exit Sub
        End If
 
        Set PT1 = Nothing: Set pvtField1 = Nothing
    End If
End Sub

 

[olympiac]

That's great.

Thanks!

 

[olympiac]

An alternative way suggested by Rijnsent can be found in the following thread: "Incomprehensible or impossible?"

Thanks