Create a newworksheet with VBA code and put VBA code in the new worksheet module

[ceshelman]

I need to use VBA to create a new sheet (got that part taken care of) but then I need to add a Private Sub Worksheet_Change(ByVal Target As Range) subroutine to the page that I have just created. How do I go about accomplishing that?

[Norie]

Check this link Programming To The Visual Basic Editor

[Damon Longworth]

This is easy if you use the Move or Copy routine when creating the new sheet.
Create a "template" sheet with your desired layout and worksheet change code.
You can hide this sheet if you desire.

Now have VBA copy this sheet for your new sheet and the code will already be
there.

Something similar to:

Sheets("YourTemplateSheet").Copy Before:=Sheets(1)

"ceshelman" wrote:

>
> I need to use VBA to create a new sheet (got that part taken care of)
> but then I need to add a Private Sub Worksheet_Change(ByVal Target As
> Range) subroutine to the page that I have just created. How do I go
> about accomplishing that?
>
>
> --
> ceshelman
> ------------------------------------------------------------------------
> ceshelman's Profile: http://www.excelforum.com/member.php...o&userid=24314
> View this thread: http://www.excelforum.com/showthread...hreadid=379209
>
>

[mp112849]

Damon, will this work if you want to copy code into the "ThisWorkbook"
module of a second workbook? In other words, here's a scenario...

I have a workbook open, call it WorkBookA. I open another existing
workbook programatically or with file/open, call it WorkBookB. Can I
copy code from the ThisWorkbook module of WorkBookA to the ThisWorkBook
module of WorkBookB? Can this code be an overwrite of an existing
method in WorkBookB, say a pre-existing ThisWorkbook sub like:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
.... code goes here
End Sub

[Damon Longworth]

Yes, it is possible. Follow the link provided by Norie for instructions. Read
the whole page so you have a good understanding of the pros and cons of
writing code with code.

I personally, will create a template with the code already present and then
add data to this template with code. I prefer the K.I.S.S. principle.

"mp112849" wrote:

> Damon, will this work if you want to copy code into the "ThisWorkbook"
> module of a second workbook? In other words, here's a scenario...
>
> I have a workbook open, call it WorkBookA. I open another existing
> workbook programatically or with file/open, call it WorkBookB. Can I
> copy code from the ThisWorkbook module of WorkBookA to the ThisWorkBook
> module of WorkBookB? Can this code be an overwrite of an existing
> method in WorkBookB, say a pre-existing ThisWorkbook sub like:
>
> Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
> Target As Range, Cancel As Boolean)
> .... code goes here
> End Sub
>
>