Is it possible to automatically input VBA code when a new sheet is generated?

Question

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

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.

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?

I did figure out that I can create another template, have the code already in there. Then instead of creating a new sheet, have it copy the template and rename that copied template. I could also make the template invisible. I guess this will work. I would still like to know if it is possible to put code in a new sheet using script though, which would be my preferred method. If someone has an idea please let me know. Thanks! — Chris
– Chris, Commented Dec 8, 2013 at 8:47

Siddharth Rout · Accepted Answer · 2013-12-08 15:47:21Z

3

Here is a sample code which will insert

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

in a new sheet in a new workbook.

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.

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

answered Dec 8, 2013 at 15:47

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

This is beautiful thanks! To get it to work within the same workbook without creating a new workbook I removed the two lines Set VBC & Set CM. I also changed Set wb = ThisWorkBook. Instead of Set ws = wb.Sheets(1), I put the string of the sheet that was generated in the place of the 1. Right after your Dim statements I just did the add sheet command and specified where it should go. This is brilliant! Thanks!

Siddharth Rout Over a year ago

Glad to be of help :)

Graffl · Accepted Answer · 2013-12-08 08:59:06Z

1

I am not aware of an easy way to put code in an Excel file. Someone might think about changing the XML structure directly (xlsx files are basically a zipped directory of xml and code files). But did you consider using XLAM (Excel addin) files, that can contain code and be imported for all users, who ever need to use it. And would open up with Excel, when the users start it. Depending on your setup, this could help you?

answered Dec 8, 2013 at 8:59

Graffl

3901 gold badge3 silver badges9 bronze badges

Comments

richardtallent · Accepted Answer · 2013-12-08 09:10:43Z

0

Look into the VBProject property of the workbook you are generating. You should be able to manipulate it, adding new VBComponents items containing the code you want.

http://msdn.microsoft.com/en-us/library/office/ff194737.aspx

This will require the appropriate security settings to do.

Here's a thread on another site related to this topic:

http://www.mrexcel.com/forum/excel-questions/663848-access-vbulletin-project-not-trusted.html

I haven't tried it myself, so I can't give much more detail.

answered Dec 8, 2013 at 9:10

richardtallent

35.5k14 gold badges87 silver badges126 bronze badges

Collectives™ on Stack Overflow

Is it possible to automatically input VBA code when a new sheet is generated?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related