Excel VBA Run-time error 1004 - Method 'Worksheets' of object '_Global' failed

jdserras

New Member
Joined
Feb 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
1613749721893.png


When opening an Excel file with VBA code (received by e-mail or copied and paste to different folder), the first time after enable the Macros (protected view) I have Run-time error 1004: Method 'Worksheets' of object '_Global' failed when an initial form opens. I press the button "End", workbook closes and, when re-open it again runs without problem.
If the same file is copied from my onedrive folder runs without error with same conditions...

I tried many different ways and I don´t find the solution.

I will be very gratefull if someone could guide me with this issue.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,116
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

If it's only happening when the workbook opens in protected view, I suspect the problem is this: Object Model calls may fail from WorkbookOpen event when exiting Protected View

You may be able to solve the problem by making the workbook's location a trusted location.

The VBA workaround, if you have access to the code, is along these lines:

VBA Code:
'In a code module
Public bDeferredOpen As Boolean
Public OpenHandler As WorkbookOpenHandler
'In ThisWorkbook module
Private Sub Workbook_Open()

    Set OpenHandler = New WorkbookOpenHandler
    Set OpenHandler.oApp = Application

End Sub
'In a class module: WorkbookOpenHandler
Public WithEvents oApp As Excel.Application
Private Sub oApp_WorkbookActivate(ByVal wb As Workbook)
    
    If bDeferredOpen Then
        bDeferredOpen = False
        Call WorkbookOpenHandler(wb)
    End If

End Sub
Private Sub oApp_WorkbookOpen(ByVal wb As Workbook)
    
    Dim oProtectedViewWindow As ProtectedViewWindow
    
    On Error Resume Next
    Set oProtectedViewWindow = oApp.ProtectedViewWindows.Item(wb.Name)
    On Error GoTo 0
    
    If oProtectedViewWindow Is Nothing Then
        bDeferredOpen = False
        Call WorkbookOpenHandler(wb)
    Else
        bDeferredOpen = True
    End If

End Sub
Private Sub WorkbookOpenHandler(ByVal wb As Workbook)

    'This is the place to put your code that would normally be in Workbook_Open

End Sub
 

jdserras

New Member
Joined
Feb 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

If it's only happening when the workbook opens in protected view, I suspect the problem is this: Object Model calls may fail from WorkbookOpen event when exiting Protected View

You may be able to solve the problem by making the workbook's location a trusted location.

The VBA workaround, if you have access to the code, is along these lines:

VBA Code:
'In a code module
Public bDeferredOpen As Boolean
Public OpenHandler As WorkbookOpenHandler
'In ThisWorkbook module
Private Sub Workbook_Open()

    Set OpenHandler = New WorkbookOpenHandler
    Set OpenHandler.oApp = Application

End Sub
'In a class module: WorkbookOpenHandler
Public WithEvents oApp As Excel.Application
Private Sub oApp_WorkbookActivate(ByVal wb As Workbook)
   
    If bDeferredOpen Then
        bDeferredOpen = False
        Call WorkbookOpenHandler(wb)
    End If

End Sub
Private Sub oApp_WorkbookOpen(ByVal wb As Workbook)
   
    Dim oProtectedViewWindow As ProtectedViewWindow
   
    On Error Resume Next
    Set oProtectedViewWindow = oApp.ProtectedViewWindows.Item(wb.Name)
    On Error GoTo 0
   
    If oProtectedViewWindow Is Nothing Then
        bDeferredOpen = False
        Call WorkbookOpenHandler(wb)
    Else
        bDeferredOpen = True
    End If

End Sub
Private Sub WorkbookOpenHandler(ByVal wb As Workbook)

    'This is the place to put your code that would normally be in Workbook_Open

End Sub
Thanks for your reply.
Unlocking the file, safety properties, runs without run-time errors.
I will try the code to verify if works.
 

jdserras

New Member
Joined
Feb 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

If it's only happening when the workbook opens in protected view, I suspect the problem is this: Object Model calls may fail from WorkbookOpen event when exiting Protected View

You may be able to solve the problem by making the workbook's location a trusted location.

The VBA workaround, if you have access to the code, is along these lines:

VBA Code:
'In a code module
Public bDeferredOpen As Boolean
Public OpenHandler As WorkbookOpenHandler
'In ThisWorkbook module
Private Sub Workbook_Open()

    Set OpenHandler = New WorkbookOpenHandler
    Set OpenHandler.oApp = Application

End Sub
'In a class module: WorkbookOpenHandler
Public WithEvents oApp As Excel.Application
Private Sub oApp_WorkbookActivate(ByVal wb As Workbook)
   
    If bDeferredOpen Then
        bDeferredOpen = False
        Call WorkbookOpenHandler(wb)
    End If

End Sub
Private Sub oApp_WorkbookOpen(ByVal wb As Workbook)
   
    Dim oProtectedViewWindow As ProtectedViewWindow
   
    On Error Resume Next
    Set oProtectedViewWindow = oApp.ProtectedViewWindows.Item(wb.Name)
    On Error GoTo 0
   
    If oProtectedViewWindow Is Nothing Then
        bDeferredOpen = False
        Call WorkbookOpenHandler(wb)
    Else
        bDeferredOpen = True
    End If

End Sub
Private Sub WorkbookOpenHandler(ByVal wb As Workbook)

    'This is the place to put your code that would normally be in Workbook_Open

End Sub
I tried the code and I got the error:

1614094121369.png
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,116
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You need to create a class module called WorkbookOpenHandler, as indicated in Post #2.

Here's a slightly modified version of the workbook: WorkbookOpenHandler.xlsm, with changes as follows:

VBA Code:
'Code module
Public Const HANDLER_ENABLED = False
'ThisWorkbook module
Private Sub Workbook_Open()
   
    If HANDLER_ENABLED Then
        Set OpenHandler = New WorkbookOpenHandler
        Set OpenHandler.oApp = Application
    Else
        ActiveWindow.DisplayHeadings = False    'Runtime error here if HANDLER_ENABLED = False
    End If

End Sub
'In class module WorkbookOpenHandler
Private Sub WorkbookOpenHandler(ByVal wb As Workbook)

    'This is the place to put your code that would normally be in Workbook_Open
    ActiveWindow.DisplayHeadings = False    'No runtime error here when HANDLER_ENABLED = True

End Sub

My experience is that if I save the workbook with HANDLER_ENABLED = False, and open in Protected View, then when I click the Enable Editing button, I get a Runtime error 91: Object variable or with block variable not set on the code line: ActiveWindow.DisplayHeadings = False

With HANDLER_ENABLED = True, the same code line works fine in Sub WorkbookOpenHandler.
 
Last edited:

jdserras

New Member
Joined
Feb 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
You need to create a class module called WorkbookOpenHandler, as indicated in Post #2.

Here's a slightly modified version of the workbook: WorkbookOpenHandler.xlsm, with changes as follows:

VBA Code:
'Code module
Public Const HANDLER_ENABLED = False
'ThisWorkbook module
Private Sub Workbook_Open()
  
    If HANDLER_ENABLED Then
        Set OpenHandler = New WorkbookOpenHandler
        Set OpenHandler.oApp = Application
    Else
        ActiveWindow.DisplayHeadings = False    'Runtime error here if HANDLER_ENABLED = False
    End If

End Sub
'In class module WorkbookOpenHandler
Private Sub WorkbookOpenHandler(ByVal wb As Workbook)

    'This is the place to put your code that would normally be in Workbook_Open
    ActiveWindow.DisplayHeadings = False    'No runtime error here when HANDLER_ENABLED = True

End Sub

My experience is that if I save the workbook with HANDLER_ENABLED = False, and open in Protected View, then when I click the Enable Editing button, I get a Runtime error 91: Object variable or with block variable not set on the code line: ActiveWindow.DisplayHeadings = False

With HANDLER_ENABLED = True, the same code line works fine in Sub WorkbookOpenHandler.
Thanks for your reply. I tried with your modifications and don´t run the workbook open code because the HANDLER_ENABLED is false and code doesn´t go inside the first if condition.
 

jdserras

New Member
Joined
Feb 19, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
The code isn´t not working in my file. I got the Run-time error:

1614158567992.png


There's any way to unlock automatically a file received from other laptop or e-mail?

1614158484119.png
 

Watch MrExcel Video

Forum statistics

Threads
1,126,897
Messages
5,621,499
Members
415,845
Latest member
marej123

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