将代码复制到新创建的工作簿的ThisWorkbook模块(VBA) [英] Copy code to newly created workbooks' ThisWorkbook module (VBA)
问题描述
我有一个宏,可以创建5个新工作簿并向其中填充一些信息.我想确保最终用户使用默认文件名保存创建的工作簿.为此,我编写了一个工作簿事件代码,该事件代码在保存文件时运行,并自动填充文件名字段.
I have a macro that creates 5 new workbooks and populates them with some info. I want to make sure that the end user saves the created workbooks with a default filename. To do that, I wrote a workbook event code that runs upon saving the file and populates the file name field automatically.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set Wb = ThisWorkbook
Application.EnableEvents = False
Co = ThisWorkbook.Sheets(1).Name
If InStr(1, Wb.Name, Co) = 0 Then
Filename = Co & " " & Format(Now, "yyyy-mm-dd")
With Application.Dialogs(xlDialogSaveAs)
Call .Show(Filename, xlOpenXMLWorkbookMacroEnabled)
End With
Else
Wb.Save
End If
Application.EnableEvents = True
Cancel = True
End Sub
这与我想要的完全一样,但是我希望能够将这些代码放入使用VBA创建的所有5个工作簿的ThisWorkbook
模块中.
This works exactly like I want it to but I want to be able to put this code in all 5 workbooks' ThisWorkbook
module as they are created using VBA.
有没有办法完成这项任务?
Is there a way to accomplish this task?
推荐答案
是.在VBA中,您需要添加对Microsoft Visual Basic For Applications Extensibility 5.3
的引用(或您所拥有的任何版本).在对象浏览器中,您会注意到一个名为VBIDE的新库.
Yes. In VBA you need to add a reference to Microsoft Visual Basic For Applications Extensibility 5.3
(or whatever version you have). In your Object Browser you will notice a new library called VBIDE.
您需要在Trust Center...Macro Settings
中更改开发人员Marco设置",以便在"Trust access to the VBA object model."
上打勾.
You will need to change the Developer Marco Settings in Trust Center...Macro Settings
so that "Trust access to the VBA object model."
is ticked.
A good summary with example is here.
这篇关于将代码复制到新创建的工作簿的ThisWorkbook模块(VBA)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!