Thread: create new workbook and sheet with vba code in it

Hello everyone this is my first post here and I hope I am explaining myself well. I have a macro that creates a new workbook with sever worksheets in it. The new worksheets get formatted with color, words and math formulas in it. This part all works perfect. The part that I can not figure out it how to add VBA to a particular sheet or sheet object (not a userform or module) . I am trying to add the code :

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If target.Address = "$D$1" Then caly2.Show
End Sub

So if some clicks in cell D1 on a worksheet it will trigger a userform (this userform opens up the calendar so that they can select a date, that then populates the cell, this part works fine if I manually enter the code by right clicking on the tab, selecting view code and entering it. So again how do I create this new tab/worksheet with this code already in it.

I am in excel 2007.

Thanks in advance
Eric

Hi Eric,

The simplist way is to have a worksheet template set up. Let's say Sheet1 is an empty sheet with that Worksheet_SelectionChange code you posted in it's code module. Then we can use the Worksheet.Copy method to create a new sheet:

Sub foo()

    Sheet1.Copy after:=Worksheets(Worksheets.Count)
    
End Sub

The created sheet will have the event handler code in too.


A second option would be to create a new sheet using Worksheets.Add and then programmatically add code to its class module.

Hi Eric,

I can not do the sheet copy because I have a macro that creates a new workbook with several sheets. So there is no sheet to copy, they are all new.

That's even better then.... why don't you just set up an entire workbook template which you can use as a basis for creating the new workbook?

Sub foo()

    Dim wkbNew As Workbook
    
    Set wkbNew = Workbooks.Add(template:="C:\My Templates\My Workbook With Code In.xls")
    
    'etc...
    
End Sub

Have you considered how this new workbook will be able to show the userform which is contained in a different workbook, or is that the next challenge?

ok making some progress, I found this line of code that allows me to create a new worksheet and adds a Worksheet_SelectionChange(ByVal Target As Range) to the new worksheet named "test". Now I just need to figure out how to add the line:

If Target.Address = "$D$1" Then caly2.Show

to the event.

Sub AddCodeWithCode()
Dim strProcLine As String

'This procedure adds a new worksheet to the active workbook.
Sheets.Add.Name = "Test"

With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule

    'Add the empty procedure
    .CreateEventProc "SelectionChange", "Worksheet"

End With
End Sub

ActiveWorkbook refers to the workbook currently open. If the code is not in the active workbook then it will save the currently active workbook, if you want to save the workbook containing the code use ThisWorkBook instead.