生成新工作表时是否可以自动输入VBA代码？ [英] Is it possible to automatically input VBA code when a new sheet is generated?

问题描述

是否可以创建一个新的Excel表，然后VB代码自动放入该表？

Is it possible to have a new Excel sheet created and then VB code automatically put in that sheet?

这是我迄今为止所做的。名为Template的工作表具有用户需要输入的所有信息的输入。我有各种检查，以确保所有字段都已填写，并在其他任何操作执行之前正确填写。当他们点击某个单元格执行脚本时，它将打开一个Word文档并导入其中的所有必需信息。然后创建一个Excel中的新工作表。根据模板表中的ComboBox（cboSites）中选择的内容，将给出新表格的名称。我也有一个支票，以确保已经没有一个名称相同的工作表。我有这些工作没有任何问题。

Here is what I have done so far. A sheet called Template has the input for all of the information that users need to input. I have various checks to make sure that all fields are filled out and are filled out correctly before anything else will execute. When they click on a certain cell to execute the script it will open a Word document and import all required information in it. Then a new sheet in Excel is created. A name is given to the new sheet, based on what was selected in the ComboBox (cboSites) from the Template sheet. I also have a check in place to make sure there already isn't a sheet with the same name. I have all of this working without any issues.

从这里我想做什么，不能想到如何做，是当新的表格是创建我想要VBA代码自动转储在这个新的工作表。我知道我想要使用的代码，但我不知道如何得到它，所以它会自动把代码与该表。

From here what I would like to do and can't think of how to do it, is when the new sheet is created I want VBA code automatically dumped in this new sheet. I know the code that I want to use, but I just have no idea how to get it so it will automatically put that code with that sheet.

这是可能的做或不能只创建一个新的工作表并格式化，而无法导入任何代码？

Is this possible to do or can only a new sheet be created and formatted, without being able to import any code into it?

推荐答案

这是一个示例代码将插入

Here is a sample code which will insert

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "Hello World"
End Sub

Sub Sample()
    Dim wb As Workbook, ws As Worksheet
    Dim VBP As Object, VBC As Object, CM As Object
    Dim strProcName As String

    Set wb = Workbooks.Add
    Set ws = wb.Sheets(1)

    Set VBP = wb.VBProject
    Set VBC = VBP.VBComponents(ws.Name)
    Set CM = VBC.CodeModule

    strProcName = "Worksheet_SelectionChange"

    With wb.VBProject.VBComponents( _
    wb.Worksheets(ws.Name).CodeName).CodeModule
        .InsertLines Line:=.CreateEventProc("SelectionChange", "Worksheet") + 1, _
        String:=vbCrLf & _
        "    Msgbox ""Hello World"""
    End With
End Sub

请根据您的需要进行修改。

Please amend it to suit your needs.

您需要确保信任访问Visual Basic项目被选中。要选择它，请按照此链接中提及的步骤

You need to ensure that Trust access to Visual Basic Project is selected. To select it, follow the steps mentioned in THIS LINK

这篇关于生成新工作表时是否可以自动输入VBA代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！