启用编辑时，Workbook_Open()sheet.activate保护视图中的错误 [英] Workbook_Open() sheet.activate error in protected view when enable editing

问题描述

当文件处于保护模式(因为是从互联网下载)时， Workbook_Open()中包含的 sheets.activate 出现问题.一旦您启用编辑"，Workbook_Open()就会被执行，并出现错误.

I have problems with sheets.activate contained into Workbook_Open() when the file is in protected mode (because downloaded from internet). As soon as you "Enable Editing" the Workbook_Open() is executed and the error comes out.

如果我使用此代码:

Private Sub Workbook_Open()
Sheets(2).Activate
End Sub

我有此错误:运行时错误1004 Worksheet类的激活方法失败

在其他讨论中，我尝试使用 Workbook_Activate()方法，如果我在所有项目中只有一个简单的激活方法，该方法将起作用.如果使用以下示例，则可以修复前面的示例:

Reading in some other discussion i tried using the Workbook_Activate() method which works if I have only one simple activation in all my project. The previous example could be fixed if I use:

Private Sub Workbook_Open()
a = True
End Sub
Private Sub Workbook_Activate()
If a Then
    Sheets(2).Activate
    a = False
End If
End Sub

但是它部分解决了问题，该代码可以工作，但是下次我有另一个sheets.activate在我的项目中时，错误再次出现(即，如果我单击面板中的按钮或运行其他例程).

but it partially fix the problem, this code could work but next time i have another sheets.activate in my project the error comes out again, (i.e. if i click a button into a panel or if i run other routines).

此错误仅在您第一次打开文件时出现，如果您停止调试器，则在不保存的情况下关闭文件并重新打开文件，该错误不会再出现，但是我会避免它在第一次出现

This error comes out only the first time you open the file, if you stop the debugger, close the file without saving and reopen the file the error doesn't come out again but i would avoid it to come out the first time

预先感谢

推荐答案

这似乎是一个已知问题:

This looks like it is a known issue:
Object Model calls may fail from WorkbookOpen event when exiting Protected View

它说...

解决方案
您可以通过以下任一方法解决此问题:-

Resolution
You can workaround the issue by either :-

1. 如果信任从中打开工作簿的位置，则将该位置添加到Excel的受信任位置".

1. If the location from where the workbooks are being open is trusted, add that location to the Excel's Trusted Locations.

将对象模型调用从WorkbookOpen事件的外部延迟到WorkbookActivate事件.

Defer Object Model calls to outside of the WorkbookOpen event to WorkbookActivate event.

延迟对象模型调用的代码示例

Code example to defer object model calls

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

这篇关于启用编辑时，Workbook_Open()sheet.activate保护视图中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！