How to display a MsgBox in case of error 1004

Lal Sahab 0
Viewed 43

Created this code to change pivot fields in 4 different workbooks.

Option Explicit

Sub Account_Name()
    
    Workbooks.Open "C:\Book1.xlsm"
    Workbooks.Open "C:\Book2.xlsx"
    Workbooks.Open "C:\Book3.xlsx"
    Workbooks.Open "C:\Book4.xlsx"

    Dim workbookNames As Variant
    workbookNames = Array("Book1.xlsm", "Book2.xlsx", "Book3.xlsx", "Book4.xlsx")
    
    Dim i As Long
    For i = LBound(workbookNames) To UBound(workbookNames)
        
        Dim wb As Workbook
        Set wb = Workbooks(workbookNames(i))
        
        Dim ws As Worksheet
        Set ws = wb.Worksheets("Analysis")
        
        Dim rootAccount As String
        rootAccount = ws.Cells(1, 6).Value

        Dim pt As PivotTable
        For Each pt In ws.PivotTables
            With pt
                With .PivotFields("Root Account")
                    .ClearAllFilters
                    .CurrentPage = rootAccount
                End With
            End With
            Next pt
    Next i

End Sub

Code is working fine until Root Account is found in all 4 workbooks but if any Root Account is not available in any of the workbooks it gives Runtime Error 1004. In such case, is it possible to clear filter from Root Account and display MsgBox that this Root Account is not available. TIA

msgbox pivot-table vba excel
1 Answers

On Error Resume Next When Single Statement

' Declare a Long variable to hold the error number.
Dim cpErrNum As Long

With .PivotFields("Root Account")
    .ClearAllFilters
    
    ' Attempt to...
    On Error Resume Next ' defer error trapping (ignore errors)
        .CurrentPage = rootAccount ' ... do this.
        cpErrNum = Err.Number ' store the error number in the variable
    On Error GoTo 0 ' disable error handling
    If cpErrNum <> 0 Then ' an error occurred
        MsgBox "Root Account is not available.", vbExclamation
    'Else ' no error occurred i.e. 'cpErrNum = 0'; do nothing
    End If

End With
  • Note that objects are handled differently i.e. there is no need for the Long variable and you check if the object was not referenced with e.g. If obj Is Nothing Then.
VBasic20080
Related