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

ddander54

Board Regular
Joined
Oct 18, 2012
Messages
75
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

VBA Code:
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,715
Office Version
  1. 365
Platform
  1. Windows
A brute force approach
VBA Code:
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
 
Solution

ddander54

Board Regular
Joined
Oct 18, 2012
Messages
75
Fluff,

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

Error.jpg
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,563
Office Version
  1. 2010
Platform
  1. Windows
According to Excel basics the direct way VBA demonstration to check if both workbook and worksheet exist :​
VBA Code:
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,563
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

A variation :​
VBA Code:
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
 

ddander54

Board Regular
Joined
Oct 18, 2012
Messages
75

ADVERTISEMENT

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

Board Regular
Joined
Oct 18, 2012
Messages
75
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,715
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,563
Office Version
  1. 2010
Platform
  1. Windows
Excel basics means Excel easy features like a filter, an advanced filter, a sort or here just a worksheet function …​
 

Forum statistics

Threads
1,143,835
Messages
5,721,076
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top