Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Object Model calls may fail from WorkbookOpen event when exiting Protected View

Object Model calls may fail from WorkbookOpen event when exiting Protected View

View products that this article applies to.

Symptoms

Consider the following scenario :-
  • 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.

↑ Back to the top

Cause

Clicking on "Enable Editing" transitions the workbook from protected view to normal view. While transitioning, the WorkbookOpen event is fired before the protect view workbook is closed, resulting into failure on object model calls.

↑ Back to the top

Resolution

You can workaround the issue by either :-

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.

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top

More Information

Below is a sample VBA code that demonstrates how you could defer Object Model calls to the 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


↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2745652
Revision : 1
Created on : 1/7/2017
Published on : 9/18/2012
Exists online : False
Views : 294