如果Excel或Word已打开，则ThisWorkbook Workbook_Open无法显示用户窗体 [英] ThisWorkbook Workbook_Open fails to show userform if Excel or Word already open

问题描述

我使用Office365.我有一个名为Test.xlsm的Excel文件，其中包含一个名为frmMain的用户窗体.在ThisWorkbook对象中，然后在Workbook_Open事件中，我有以下代码:

  Private Sub Workbook_Open()工作表(主要").激活frmMain.Show结束子 

它旨在每次打开Test.xlsm文件时启动(显示)frmMain用户窗体.
但是，如果已经打开另一个Excel或Word文件，则frmMain用户表单无法启动/显示.
有没有一种方法可以打开Excel或Word文件，并且仍然能够启动和使用Test.xlsm文件及其用户窗体frmMain?这是Office365的问题吗?

更新:

还尝试将应用程序安全性设置为较低，以防其默认为msoAutomationSecurityByUI:

  Private Sub Workbook_Open()作为frmMain的dim frmDim secAutomation作为MsoAutomationSecurity设置frm =新的frmMainsecAutomation = Application.AutomationSecurityApplication.AutomationSecurity = msoAutomationSecurityLow工作表(主要").激活frmMain.Show结束子 

更新:

还尝试将其放置在独立模块上(不在ThisWorkbook中):

  Private Sub runForm()frmMain.Show结束子 

然后从ThisWorkbook>中这样称呼它Workbook_Open事件:

  Private Sub Workbook_Open()Application.OnTime VBA.Now，文件名'！runForm.runForm"结束子 

在关闭所有其他Excel的同时，这也会打开Test.xlsm和用户窗体，但是当已经打开.xlsx时，这是同样的问题-打开了文件但没有打开用户窗体.

解决方案

打开另一个工作簿时，对于新打开的工作簿，Workbook_Open事件无法正确触发.这是一个错误.

解决此问题的一种方法是利用自定义RibbonUI对象，以便在打开工作簿时可以触发事件.

设置起来并不简单，但只需要做一次.需要3个步骤:

1)在ThisWorkbook对象中设置Friend方法
在 ThisWorkbook 模块中编写以下代码:

 选项显式私有m_openAlreadyRan为布尔值Friend Sub FireOpenEventIfNeeded(布尔值可选dummyVarToMakeProcHidden)如果不是m_openAlreadyRan，则为Workbook_Open结束子私人子Workbook_Open()结束子 

注意几件事:
a)需要使用哑元参数来隐藏 Macros 框(Alt + F8)
中的方法b)该方法声明为 Friend ，因此该项目只能访问该方法
c)需要一个布尔变量(m_openAlreadyRan).稍后将在第3阶段使用它

2)将CustomRibbon嵌入到工作簿中
首先，我们需要一些代码来调用在步骤1中创建的方法.
在您的工作簿中创建一个标准模块，并将其命名为 CustomUI .将以下代码添加到 CustomUI 模块:

 选项显式公共子InitRibbon(功能区为IRibbonUI)ThisWorkbook.FireOpenEventIfNeeded结束子 

初始化功能区时需要调用此方法.换句话说，此方法是功能区使用的回调方法.

这是棘手的部分.可以通过几种方式完成此操作，您可以在网络上找到一些工具.但是，我将向您展示如何手动进行操作:
a)关闭并保存您的工作簿

b)如果不使用存档程序，请下载.我使用的是免费的

e)创建一个名为 customUI 的文件夹.您会注意到Workbook文件实际上是文件的一个实现

f)打开记事本或任何文本编辑器，并使用以下xml创建一个新文件:

 < customUI xmlns =" http://schemas.microsoft.com/office/2006/01/customui"onLoad ="InitRibbon"</customUI> 

请注意 onLoad 回调的名称与VBA中创建的方法相匹配，即 InitRibbon

g)(任意位置)将文本文件另存为 customUI.xml (确保您没有双扩展名(例如.xml.txt)

)

h)将.xml文件拖放到存档器内的customUI文件夹中

i)返回上一级并打开 _rels 文件夹.您应该看到一个 .rels 文件

j)编辑 .rels 文件(右键单击，然后编辑"应打开一个记事本)

k)添加xml:

 <关系ID ="rId10"；类型="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility"目标="customUI/customUI.xml"/> 

此操作必须在结束的</Relationships> 标记之前.不后.我使用了rId10，但是您可以查看文件中的所有其他rId编号，然后选择下一个可用的编号.确保您不复制现有的rId

l)将您的编辑保存到文件中并退出存档，同时确保还保存了存档编辑(如果您将7-Zip与确定/取消"框一起使用，则系统会提示您)

m)关闭存档器.您已经完成了

3)设置Workbook_Open事件
我们需要考虑在步骤1中创建的布尔值(这样我们就不会再次运行相同的代码)和窗口视图的受保护状态.
将此替换为步骤1(在ThisWorkbook中):

 选项显式私有m_openAlreadyRan为布尔值私有m_isOpenDelayed为布尔Friend Sub FireOpenEventIfNeeded(布尔值可选dummyVarToMakeProcHidden)如果不是m_openAlreadyRan，则为Workbook_Open结束子私人子Workbook_Activate()如果m_isOpenDelayed然后m_isOpenDelayed =假初始化工作簿万一结束子私人子Workbook_Open()m_openAlreadyRan =真昏暗的objProtectedViewWindow为ProtectedViewWindow'关于错误继续设置objProtectedViewWindow = Application.ProtectedViewWindows(Me.Name)出错时转到0'm_isOpenDelayed =否(objProtectedViewWindow无效)如果不是m_isOpenDelayed，则为InitWorkbook结束子私人子InitWorkbook()如果VBA.Val(Application.Version)<12然后MsgBox此工作簿需要Excel 2007或更高版本！"，vbCritical，关闭".我关闭错误退出子万一'使用新的frmMain.表演'其他代码'''结束于结束子 

注意以下几点:
a)在保护窗口视图的情况下， _Open 事件代码会延迟到 _Activate 事件.
b) _Open 和 _Activate 都指向 InitWorkbook 方法.在这里，您可以在工作簿打开时添加需要运行的代码.
c)在 _Open 事件中将 m_openAlreadyRan 设置为True，以便 FireOpenEventIfNeeded 方法不会不必要地调用 _Open (即当由于没有其他书籍打开而导致错误未发生时
d)我使用的 frmMain 的 New 实例与 @ArcherBird 完全相同.通过调用 frmMain.Show 使用表单的全局实例是一种不好的做法.另外，您可以创建一个变量来代替 New New frmMain :

  Dim f As New frmMainf.显示 

I use Office365. I have an Excel file called Test.xlsm which contains a userform named frmMain.
In the ThisWorkbook object and then in the Workbook_Open event I have the following code:

    Private Sub Workbook_Open()
       Worksheets("Main").Activate
       frmMain.Show
    End Sub

It is meant to launch (show) the frmMain userform every time the Test.xlsm file opens.
However, if another Excel or Word file is already open, the frmMain user form fails to launch/show.
Is there a way to have an Excel or Word file already open and still be able to launch and use the Test.xlsm file and its userform frmMain ? is this a problem with Office365?

UPDATE:

Also tried setting the application security to low in case it was defaulting to msoAutomationSecurityByUI:

Private Sub Workbook_Open()

dim frm As frmMain
Dim secAutomation As MsoAutomationSecurity

set frm = New frmMain
secAutomation = Application.AutomationSecurity

Application.AutomationSecurity = msoAutomationSecurityLow
Worksheets("Main").Activate
frmMain.Show

End Sub

UPDATE:

Also tried placing this on a stand alone module (not in ThisWorkbook):

Private Sub runForm()
   frmMain.Show
End Sub

Then calling it like this from the ThisWorkbook > Workbook_Open event:

Private Sub Workbook_Open()
   Application.OnTime VBA.Now, "name of file '!runForm.runForm"
End Sub

With all other Excel closed, this also opens Test.xlsm and the userform, but when an .xlsx is already open then it is the same problem - opened the file but it didn't open the userform.

解决方案

When another Workbook is opened, Workbook_Open events do not fire properly for newly opened workbooks. This is a bug.

One way to solve this is to take advantage of a Custom RibbonUI Object so that you can have an event firing when the Workbook is opened.

This is not simple to set up but you only have to do it once. There are 3 steps needed:

1) Set up a Friend method in the ThisWorkbook object
Write the following code inside the ThisWorkbook module:

Option Explicit

Private m_openAlreadyRan As Boolean

Friend Sub FireOpenEventIfNeeded(Optional dummyVarToMakeProcHidden As Boolean)
    If Not m_openAlreadyRan Then Workbook_Open
End Sub

Private Sub Workbook_Open()
End Sub

Note a few things:
a) the dummy parameter is needed to hide the method from the Macros box (Alt+F8)
b) the method is declared as Friend so it is only accessible to this project
c) a boolean variable is needed (m_openAlreadyRan). This will be used later at stage 3

2) Embed a CustomRibbon into your Workbook
First we need some code to call the method created at step 1.
Create a standard module in your Workbook and call it CustomUI. Add the following code to the CustomUI module:

Option Explicit

Public Sub InitRibbon(ribbon As IRibbonUI)
    ThisWorkbook.FireOpenEventIfNeeded
End Sub

This method needs to be called when the Ribbon is initialized. In other words, this method is a callback method used by the Ribbon.

This is the tricky part. This can be done is several ways and you can find some tools on the web. However, I will show you how to do it manually:
a) Close and Save your Workbook

b) Download an archiver program if you don't use one. I use 7-Zip which is free

c) Open the archiver and browse to your Workbook folder

d) Right-click the Workbook and choose Open inside

e) Create a folder called customUI. You will have noticed that the Workbook file is actually an achive of files

f) Open Notepad or any text editor and create a new file with the following xml:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="InitRibbon"></customUI>

Notice that the name of the onLoad callback matches the method created in VBA i.e. InitRibbon

g) Save text file (anywhere) as customUI.xml (make sure you don't have double extension (e.g. .xml.txt)

h) Drag and drop the .xml file in the customUI folder inside the archiver

i) Go back one level and open the _rels folder. You should see a .rels file

j) Edit the .rels file (right-click then Edit should open a Notepad)

k) Add the xml:

<Relationship Id="rId10" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>

This needs to go before the ending </Relationships> tag. Not after. I've used rId10 but you can look at all the other rId numbers in the file and choose the next available one. Make sure you do not duplicate an existing rId

l) Save your edits to the file and exit the archive while making sure you save the archive edits as well (you should be prompted if you use 7-Zip with an Ok/Cancel box)

m) Close the archiver. You're done with it

3) Set up the Workbook_Open event
We need to take into account the boolean created at step 1 (so that we don't run same code twice) and the protected state of the window view.
Replace the code at step 1 (in ThisWorkbook) with this:

Option Explicit

Private m_openAlreadyRan As Boolean
Private m_isOpenDelayed As Boolean

Friend Sub FireOpenEventIfNeeded(Optional dummyVarToMakeProcHidden As Boolean)
    If Not m_openAlreadyRan Then Workbook_Open
End Sub

Private Sub Workbook_Activate()
    If m_isOpenDelayed Then
        m_isOpenDelayed = False
        InitWorkbook
    End If
End Sub

Private Sub Workbook_Open()
    m_openAlreadyRan = True
    Dim objProtectedViewWindow As ProtectedViewWindow
    '
    On Error Resume Next
    Set objProtectedViewWindow = Application.ProtectedViewWindows(Me.Name)
    On Error GoTo 0
    '
    m_isOpenDelayed = Not (objProtectedViewWindow Is Nothing)
    If Not m_isOpenDelayed Then InitWorkbook
End Sub

Private Sub InitWorkbook()
    If VBA.Val(Application.Version) < 12 Then
        MsgBox "This Workbook requires Excel 2007 or later!", vbCritical, "Closing"
        Me.Close False
        Exit Sub
    End If
    '
    With New frmMain
        .Show
        'Other code
        '
        '
        '
    End With
End Sub

Notice the following:
a) The _Open event code is delayed to the _Activate event in case Window View is protected
b) Both _Open and _Activate point to the InitWorkbook method.This is where you add the code you need to run when the workbook opens
c) m_openAlreadyRan is set to True in the _Open event so that the FireOpenEventIfNeeded method does not call _Open unnecessarily (i.e. when the bug is not happening because there are no other books open)
d) I've used a New instance of frmMain exactly as @ArcherBird mentioned. It is considered bad practice to use the global instance of the form by calling frmMain.Show. Also, instead of With New frmMain you could just create a variable:

Dim f As New frmMain
f.Show

这篇关于如果Excel或Word已打开，则ThisWorkbook Workbook_Open无法显示用户窗体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！