Macro Error message showing even if no error?

[NormChart55]

Hello All,

I am working at getting messages included in some templates to clean up any errors that may occur and help the user. I have this which is providing the message prompt, however it does it even if the SKU is available and there is no error? basically this is copying and pasting a SKU into a pivot and sometimes they may have already had it filtered incorrectly to where the SKU number is not in the pivot. I am getting the message box in all scenarios. I am sure I have something slightly off. Thoughts?

Dim a As String
Dim pt As PivotTable

ThisWorkbook.Worksheets("Template").Activate

a = Worksheets("Template").Cells(668, 4).Value

On Error Resume Next

For Each pt In ActiveSheet.PivotTables
    With pt.PivotFields("SKU")
    .ClearAllFilters
    .CurrentPage = a
   
    End With
   
Next

MsgBox "SKU Not available in the Pivot. Check if you selected the SKU correctly and if any items are already filtered out of the pivot. If the device is new, make sure its included in the source data.", vbCritical

 

[Joe4]

That is because you have no conditions on that MsgBox and that line of code will be hit EVERY time.
You don't want to ignore the error, you want to add error handling -- something like this:

Sub MyMacro()

    Dim a As String
    Dim pt As PivotTable

    ThisWorkbook.Worksheets("Template").Activate

    a = Worksheets("Template").Cells(668, 4).Value

    On Error GoTo err_msg

    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields("SKU")
            .ClearAllFilters
            .CurrentPage = a
        End With
    Next

    On Error GoTo 0

    Exit Sub

err_msg:
    MsgBox "SKU Not available in the Pivot. Check if you selected the SKU correctly and if any items are already filtered out of the pivot. If the device is new, make sure its included in the source data.", vbCritical
 
End Sub

 

[NormChart55]

That is because you have no conditions on that MsgBox and that line of code will be hit EVERY time.
You don't want to ignore the error, you want to add error handling -- something like this:

Sub MyMacro()

    Dim a As String
    Dim pt As PivotTable

    ThisWorkbook.Worksheets("Template").Activate

    a = Worksheets("Template").Cells(668, 4).Value

    On Error GoTo err_msg

    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields("SKU")
            .ClearAllFilters
            .CurrentPage = a
        End With
    Next

    On Error GoTo 0

    Exit Sub

err_msg:
    MsgBox "SKU Not available in the Pivot. Check if you selected the SKU correctly and if any items are already filtered out of the pivot. If the device is new, make sure its included in the source data.", vbCritical
 
End Sub

thanks, makes sense. I appreciate the quick response. It is now working as intended.

 

[DanteAmor]

Hi @NormChart55

however it does it even if the SKU is available and there is no error?

Using the On Error statement will make it jump to the next statement regardless of the type of error, it can be a data error or it can be a code error.
Putting an error handler as suggested by Joe is recommended in some cases when you know what error is going to occur, but it also has its drawbacks because it will also skip any type of error and jump directly to the error message.

Ideally, try to control the error with code, whenever possible.
I show you an example of how it could be done.

Other details, do you have several pivot tables in the sheet?
If you only have one pivot table, then it could be like this:

Sub MyMacro_v2()
  Dim a As String
  Dim pt As PivotTable
  Dim itm As PivotItem
  Dim exists As Boolean
  
  ThisWorkbook.Worksheets("Template").Activate
  
  a = Worksheets("Template").Cells(668, 4).Value
  Set pt = ActiveSheet.PivotTables(1)
  pt.PivotFields("SKU").ClearAllFilters
  For Each itm In pt.PivotFields("SKU").PivotItems
    If itm = a Then
      pt.PivotFields("SKU").CurrentPage = a
      exists = True
      Exit For
    End If
  Next

  If exists = False Then
    MsgBox "SKU Not available in the Pivot. " & _
           "Check if you selected the SKU correctly and " & _
           "if any items are already filtered out of the pivot. " & _
           "If the device is new, make sure its included in the source data.", vbCritical
  End If
End Sub

 

[Joe4]

Your welcome.
Glad we were able to help!