仅当第一次打开工作簿时，如何运行宏？ [英] How can I run a macro as a workbook opens for the first time only?

问题描述

我有一本工作簿，它运行一个宏来显示用户窗体Open1，使用（非常基本的）代码：

I've got a workbook which runs a macro to show the userform Open1 as it opens, using the (very basic) code:

Private Sub Workbook_Open()

    Open1.Show

End Sub

这样做的工作不错 - 每次打开工作簿时，用户窗体弹出并运行完美。

This does its job fine - each time I open the workbook, the userform pops up and runs perfectly.

但是，我想要userform将首次在仅打开工作簿时出现。有没有办法让这种情况发生？

But, I want the userform to appear the first time the workbook is opened only. Is there a way to allow this to happen?

推荐答案

你可以使用一个虚拟模块，第一次打开电子表格...

You could use a dummy module which gets deleted the first time you open the spreadsheet...

如下所示：

If ModuleExists("DummyModule") Then
    Open1.Show
    DoCmd.DeleteObject acModule, "DummyModule"
End If

Function ModuleExists(strModuleName As String) As Boolean
    Dim mdl As Object
    For Each mdl In CurrentProject.AllModules
        If mdl.Name = strModuleName Then
            ModuleExists = True
            Exit For
        End If
    Next
End Function

---

更新： DoCmd不在excel vba中使用。这将教我编写代码而不测试它！
以下更新的代码将会起作用，但为了访问VB环境，excel需要被信任。

---

Update: as stated, DoCmd isn't used in excel vba. That will teach me to write code without testing it! The following updated code will work, but in order to access the VB environment, excel needs to be trusted.

信任中心中有一个设置>宏设置，您可以勾选此代码在开发人员宏设置下工作

There is a setting in the Trust Center>Macro Settings that you can tick for this code to work under Developer Macro Settings

因此，这可能不是打开安全问题的可能性...

As such, this may not be the way to go as it opens up the possibility of security issues...

Sub RemoveModule()
    If ModuleExists("DummyModule") Then
        Open1.Show
        Dim vbCom As Object: Set vbCom = Application.VBE.ActiveVBProject.VBComponents
        vbCom.Remove VBComponent:=vbCom.Item("DummyModule")
    End If
End Sub

Function ModuleExists(strModuleName As String) As Boolean
    Dim mdl As Object
    For Each mdl In Application.VBE.ActiveVBProject.VBComponents
        If mdl.Name = strModuleName Then
            ModuleExists = True
            Exit For
        End If
    Next
End Function

这篇关于仅当第一次打开工作簿时，如何运行宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！