- You have an Excel add-in (VBA, COM or VSTO) that captures the WorkbookOpen event and makes Object Model calls into Excel from this event handler.
- You open a Workbook in protected view (because of opening workbook from Internet, email attachment etc...) and click on "Enable Editing."
- Some Object Model calls (e.g. Sheet.Activate) being made from the WorkbookOpen event handler fail with a runtime error 1004 - Method of Object failed.
1. If the location from where the workbooks are being open is trusted, add that location to the Excel's Trusted Locations.
2. Defer Object Model calls to outside of the WorkbookOpen event to WorkbookActivate event.
Option Explicit Public WithEvents oApp As Excel.Application Private bDeferredOpen As Boolean 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 'The below line will throw error (Subscript out of range) if the workbook is not opened in protected view. Set oProtectedViewWindow = oApp.ProtectedViewWindows.Item(Wb.Name) On Error GoTo 0 'Reset error handling If oProtectedViewWindow Is Nothing Then bDeferredOpen = False Call WorkbookOpenHandler(Wb) Else 'Delay open actions till the workbook gets activated. bDeferredOpen = True End If End Sub Private Sub WorkbookOpenHandler(ByVal Wb As Workbook) 'The actual workbook open event handler code goes here... End Sub