仅当第一次打开工作簿时,如何运行宏? [英] 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屋!