Incomprehensible or impossible?

[olympiac]

Can you help please with the following query?
The code below is an excellent way to update a pivot table from another sheet but if I choose a value that doesn't exist in the pivot the data in the pivot is overwritten.

My code:


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 in Cell B7 updates the Page field of the Pivot Table "MainPivotTable"

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

 

[Rijnsent]

Hi,
before:
PT1.PivotFields("Team").CurrentPage = Sheets("Summary").Range("B7").Value

Put something like:

FoundPI = False
'Loop through all the items
For Each PI In PT1.PivotFields("Team").PivotItems
If PI.Value = Sheets("Summary").Range("B7").Value
'Found a match!
FoundPI = True
Exit For
Else
EndIf
Next PI

If FoundPI = True Then
PT1.PivotFields("Team").CurrentPage = Sheets("Summary").Range("B7").Value
Else
'Message to the user?
EndIf

Cheers,
Koen

 

[olympiac]

thanks for your answer.

There is another way as well suggested by RS411 in my previous thead " If Not Intersect Target and Pivot Table"

Cheers