Cell Trigger To Change Pivot Table

[Dale]

Small bit of help needed for 2 items.
I have a pivot table, which I want to have a user type in a value in cell
B3. 1) If the value is found in the drop down list of the pivot table on the
sheet, change the pivot table, 2) if not, display a message box saying so.
1) This works, sort of, but the code just keeps on running and running.
2) I haven’t got here yet but any suggestions would be welcome.

Tks

Private Sub Worksheet_Calculate()
Worksheet_Change Range("VB_Trigger") 'Named range on Sheet Pivot-Table",
location B3
End Su
''***********************************************************************************

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim pt As PivotTable
Dim pfStockNumber As PivotField 'The PAGE FIELD
Dim strSN As String 'Named range on Sheet Pivot-Table",
location B3
Set pt = ActiveSheet.PivotTables(1)
Set pfStockNumber = pt.PivotFields("STOCK#")
Application.ScreenUpdating = False
strSN = ActiveSheet.Range("B3").Value

' Set up the event to watch B3 single cell.
If Target.Address = Range("VB_Trigger").Address Then

ActiveSheet.PivotTables("PivotTable1").PivotFields("Stock#").CurrentPage =
strSN
Else
MsgBox "Stock Number Not Found" 'I haven't worked on this part yet.
End If

Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim pt As PivotTable
Dim pfStockNumber As PivotField 'The PAGE FIELD
Dim strSN As String 'Named range on Sheet Pivot-Table", location B3
Set pt = ActiveSheet.PivotTables(1)
Set pfStockNumber = pt.PivotFields("STOCK#")
Application.ScreenUpdating = False
strSN = ActiveSheet.Range("B3").Value

' Set up the event to watch B3 single cell.
On Error goto ErrHandler
If Target.Address = Range("VB_Trigger").Address Then
Application.EnableEvents = False
for each pi in pfStockNumber.PivotItems
if pi = strSN then
pfStockNumber.CurrentPage = pi.Value
exit for
end if
next
if pfStockNumber.CurrentPage <> strSN then
MsgBox "Stock Number Not Found"
End If
end if
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

I wouldn't call the Change event from the calculate event. Do one or the
other - whichever is appropriate.

 

[Dale]

Thanks, I was soo close and yet soo far...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim pt As PivotTable
Dim pfStockNumber As PivotField 'The PAGE FIELD
Dim strSN As String 'Named range on Sheet Pivot-Table", location B3
Set pt = ActiveSheet.PivotTables(1)
Set pfStockNumber = pt.PivotFields("STOCK#")
Application.ScreenUpdating = False
strSN = ActiveSheet.Range("B3").Value

' Set up the event to watch B3 single cell.
On Error goto ErrHandler
If Target.Address = Range("VB_Trigger").Address Then
Application.EnableEvents = False
for each pi in pfStockNumber.PivotItems
if pi = strSN then
pfStockNumber.CurrentPage = pi.Value
exit for
end if
next
if pfStockNumber.CurrentPage <> strSN then
MsgBox "Stock Number Not Found"
End If
end if
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

I wouldn't call the Change event from the calculate event. Do one or the
other - whichever is appropriate.