Macro should only run if a specific filename is open, else MsgBox

[ddander54]

Need some help to modify this code to only run if a specific file name is open. The file is a canned report from SSRS, so I can't put the macro in the workbook, therefore the macro resides in my Personal.XLSB
I have tried a few variations of things found in the forums, but keep running into an error in the code one way (file isn't open) or the other (file is open).

So what I am trying to accomplish is....
If "PPM_Resource_Utilization_Detail_Report.xlsx" is open, then run the code to rename the tabs,
else display the message box to remind me that the file isn't open.

Currently the code below runs fine if the file is open, but gives an error (subscript 9) on the Set wb line and doesn't get down to the MsgBox line

Option Explicit

Sub RenameSheetMumps()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim wb As Workbook
    
    'If Not ActiveWorkbook Is Workbooks("PPM_Resource_Utilization_Detail_Report.xlsx") Then
    'MsgBox title:="ERROR", Prompt:="Can't run this unless " & ThisWorkbook.FullName & " is the active workbook."
    'Exit Sub
    'End If
    
    Set wb = Workbooks("PPM_Resource_Utilization_Detail_Report.xlsx")
    
    If wb.name = "PPM_Resource_Utilization_Detail_Report.xlsx" Then
    
    For Each ws In Worksheets
        If ws.name = "Sheet1" Then
            ws.name = ws.Range("B7").Value
        Else
            ws.name = ws.Range("B6").Value
        End If
    Next
    
    Else
        MsgBox "Workbook not open", vbCancel
    End If
     
    Application.ScreenUpdating = True
End Sub

Thx,
Don

 

[Fluff]

A brute force approach

Sub RenameSheetMumps()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim wb As Workbook
    
    On Error Resume Next
    Set wb = Workbooks("PPM_Resource_Utilization_Detail_Report.xlsx")
    On Error GoTo 0
    
    If wb Is Nothing Then
      MsgBox Title:="ERROR", Prompt:="Can't run this unless " & ThisWorkbook.FullName & " is the active workbook."
      Exit Sub
    End If
    
    For Each ws In wb.Worksheets
        If ws.Name = "Sheet1" Then
            ws.Name = ws.Range("B7").Value
        Else
            ws.Name = ws.Range("B6").Value
        End If
    Next
     
    Application.ScreenUpdating = True
End Sub

 

[ddander54]

Fluff,

Thanks for the reply. However I got this error before to because the macro resides in my Personal.XLSB

 

[Marc L]

According to Excel basics the direct way VBA demonstration to check if both workbook and worksheet exist :​

Sub Demo1()
    If Application.IfError(Evaluate("ISREF('[PPM_Resource_Utilization_Detail_Report.xlsx)]Sheet1'!A1)"), False) Then
        With Workbooks("PPM_Resource_Utilization_Detail_Report.xlsx").Sheets("Sheet1")
            .Name = .[B7].Text
        End With
    Else
        Beep
    End If
End Sub

 

[Marc L]

A variation :​

Sub Demo1v()
    If Application.IfError(Evaluate("ISREF('[PPM_Resource_Utilization_Detail_Report.xlsx)]Sheet1'!A1)"), False) Then
        With Range("'[PPM_Resource_Utilization_Detail_Report.xlsx]Sheet1'!B7")
            .Parent.Name = .Text
        End With
    Else
        Beep
    End If
End Sub

 

[Fluff]

However I got this error

That shows the workbook isn't open, I just copied your existing error message, but that can be changed quite simply.

 

[ddander54]

Fluff,

Thanks again for the reply....I think I get it now. I changed ThisWorkbook.FullName to ActiveWorkbook.FullName and it's working as expected. Thanks much for another 'lesson'.

Don

 

[ddander54]

Marc L,

Thanks for your reply. I appreciate the 'excel basics' lesson, as I am definitely in the VBA 'basic' camp! I will work with your suggestion as well to help me learn more.

Don

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Marc L]

​

Excel basics means Excel easy features like a filter, an advanced filter, a sort or here just a worksheet function …​