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

[jdserras]

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.

 

[StephenCrump]

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:

'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]

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:

'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]

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:

'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:

 

[StephenCrump]

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:

'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.

 

[jdserras]

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:

'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]

The code isn´t not working in my file. I got the Run-time error:

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