如何为透视表筛选器捕获错误或防错子Workbook_SheetChange

Question

我需要一些帮助来捕获以下VBA代码的错误。这段代码很好地控制了2个透视表，从工作表上的单个单元格值中筛选出值。当该值未出现在筛选器中时，就会出现问题。我可以使用一种优雅的方式来简单地告诉用户，对于一个或两个透视表，该值不存在。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Worksheets("Lookup").Range("A2")) Is Nothing Then Exit 
Sub

Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim Field1 As PivotField
Dim Field2 As PivotField
Dim NewCat1 As String
Dim NewCat2 As String

Set pt1 = Worksheets("Lookup").PivotTables("PTProd")
Set Field1 = pt1.PivotFields("Material Number End")
NewCat1 = Worksheets("Lookup").Range("A2").Value

Set pt2 = Worksheets("Lookup").PivotTables("PTClaim")
Set Field2 = pt2.PivotFields("Material")
NewCat2 = Worksheets("Lookup").Range("A2").Value

With pt
Field1.ClearAllFilters
Field1.CurrentPage = NewCat1
pt1.RefreshTable
Field2.ClearAllFilters
Field2.CurrentPage = NewCat2
pt2.RefreshTable
End With

End Sub

Answer 1

我想你有很多方法可以做到这一点。这样的东西对你来说应该是有效的。

Sub HandleError()
' Your code here

On Error GoTo ErrMsg

Exit Sub

ErrMsg:
MsgBox ("Type in your message here."), , "MESSAGE TITLE"

End Sub

或者。。。

MsgBox Err.Description