create new workbook and sheet with vba code in it

[runTNT]

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 :

Please Login or Register  to view this content.

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

[Colin Legg]

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:

Please Login or Register  to view this content.

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.

[runTNT]

Colin,
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.

Also, I am not sure what you mean by programmatically add code? Can you elaborate,

Thank you

[Colin Legg]

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?

Please Login or Register  to view this content.

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?

[runTNT]

Currently I have a dashboard workbook which creates a new workbook for every project. All of the macros and userforums reside in the dashboard workbook. All of the project workbook data feed the dashboard workbook. Also each project workbook consists of 15 pages all with there own formatting and all with there own userforums and macros that all reside in the dashboard workbook. I believe this only works because the dashboard workbook is always open when a project workbook is open because you can only open a project workbook or create a project workbook from with in the dashboard workbook. I have not used a workbook template before, but I am looking into it. Is there another way to add the code to a worksheet when you create it with Sheets.Add.Name = “sheet name”

-Eric

[runTNT]

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:

Please Login or Register  to view this content.

to the event.

Please Login or Register  to view this content.

[runTNT]

Ok figured it out, for who ever wants to know and colin thanks for the help

Please Login or Register  to view this content.

[runTNT]

ok, so now my code gets inserted and works, but when I use the

Please Login or Register  to view this content.

it does not save the new code.... any reason why that is?

[royUK]

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.