将 BeforeDoubleClick_event 的模块代码添加到动态创建的工作表中 [英] Add Module code of BeforeDoubleClick_event to dynamically created worksheets

问题描述

我有这个代码:

For a = 1 To 5
    strFoglio = "SheetName" & a
    Sheets.Add

    ActiveSheet.Name = strFoglio

    ActiveSheet.Move after:=Sheets(Sheets.Count)
Next a

有没有办法在这些全新的工作表上编写代码，例如:

Is there a way to write code on these brand new sheets for example:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    Dim myRange As Range
End sub

当然，我想直接在 For...Next 循环中执行，而不是手动执行.

Naturally, I'd like to do directly in the For...Next loop and not manually.

推荐答案

下面的代码将运行你的 For 循环，创建 5 个工作表，每个工作表将调用一个 Sub CodeCopy 将模块中的代码行(在本例中为Sheet1"中的代码)复制到新创建的工作表中.

The code below will run your For loop, create 5 sheets, and per sheet will call a Sub CodeCopy which will copy the code lines from a Module (in this example the code in "Sheet1") into the new created sheet.

代码

Option Explicit

Sub CreateSheets()

Dim a As Long

For a = 1 To 5
    Sheets.Add
    ActiveSheet.Name = "SheetName" & a
    ActiveSheet.Move after:=Sheets(Sheets.Count)
    Call CodeCopy(ActiveSheet.Name)
Next a

End Sub

' **********

Sub CodeCopy(DestShtStr As String)    

' Macro to copy the macro module from sheet1 to a new Sheet 
' Name of new sheet is passed to the Sub as a String
' Must install "Microsoft Visual Basic for Applications Extensibility library" 
' from Tools > References.

Dim i           As Integer
Dim SrcCmod     As VBIDE.CodeModule
Dim DstCmod     As VBIDE.CodeModule

' set source code module to code inside "Sheet1"
Set SrcCmod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Worksheets("Sheet1").CodeName).CodeModule
Set DstCmod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Worksheets(DestShtStr).CodeName).CodeModule

' copies all code line inside "Sheet1"
' can be modified to a constant number of code lines
For i = 1 To SrcCmod.CountOfLines
   DstCmod.InsertLines i, SrcCmod.Lines(i, 1)
Next i

End Sub

Sheet1"中将被复制到所有新创建的工作表的代码是:

Code in "Sheet1" that will be copied to all new created sheets is:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim myRange As Range

End Sub

<小时>

说明

为了让这段代码正常工作，您需要允许以下 2 件事:

In order for this code to work, you need to allow the following 2 things:

1. 转到工具 >> 参考，并添加对Microsoft Visual Basic"的参考应用程序扩展性"库(下面的屏幕截图)

1. Go to Tools >> References, and add a reference to "Microsoft Visual Basic for Applications Extensibility" library (screen-shot below)

1. 在 Excel 主菜单中，进入 Developer 菜单，然后选择 Macro Security，点击 V 允许信任对 VBA 项目对象模型的访问"(下面的屏幕截图)

1. In Excel Main menu, go to Developer Menu, then select Macro Security, the click V to allow "Trust access to the VBA project object model" (screen-shot below)

这篇关于将 BeforeDoubleClick_event 的模块代码添加到动态创建的工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！