Hi,

I have a macro that creates a new worksheet, however when it does this i
want it to add some code to the worksheet_Activate event of the new sheet.
Assuming the new worksheet is called "NewYP" and the code i would like in
activate_Sheet event is

Private Sub Worksheet_Activate()

Toolbars.YPBar

End Sub

How do i go about adding the code ?

All help is appreciated

Luca

You can have VBA code write VBA code. See
www.cpearson.com/excel/vbe.htm for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Luca" wrote in message
...
Hi,

I have a macro that creates a new worksheet, however when it
does this i
want it to add some code to the worksheet_Activate event of the
new sheet.
Assuming the new worksheet is called "NewYP" and the code i
would like in
activate_Sheet event is

Private Sub Worksheet_Activate()

Toolbars.YPBar

End Sub

How do i go about adding the code ?

All help is appreciated

Luca

Luca:

try,

Sub Test()
Dim sht As Worksheet
Set sht = Sheets.Add
sht.Name = "NewYP"
With ThisWorkbook.VBProject.VBComponents.Item(sht.CodeN ame).CodeModule
.InsertLines 1, "Private Sub Worksheet_Activate()"
.InsertLines 2, ""
.InsertLines 3, "Toolbars.YPBar"
.InsertLines 4, ""
.InsertLines 5, "End Sub"
End With
End Sub

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Luca" wrote:

Hi,

I have a macro that creates a new worksheet, however when it does this i
want it to add some code to the worksheet_Activate event of the new sheet.
Assuming the new worksheet is called "NewYP" and the code i would like in
activate_Sheet event is

Private Sub Worksheet_Activate()

Toolbars.YPBar

End Sub

How do i go about adding the code ?

All help is appreciated

Luca

Your code will not work in the 'Require Variable Declaration'
option is set to true, because line 1 will be 'Option Explicit'
and your code will push this down. Instead, use CreateEventProc.

Dim InsLine As Long
Dim sht As Worksheet
Set sht = Sheets.Add

sht.Name = "NewYP"
With ThisWorkbook.VBProject.VBComponents(sht.CodeName). CodeModule
InsLine = .CreateEventProc("Activate", "Worksheet") + 1
.InsertLines InsLine, "Toolbars.YPBar"
End With




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"chijanzen" wrote in
message
...
Luca:

try,

Sub Test()
Dim sht As Worksheet
Set sht = Sheets.Add
sht.Name = "NewYP"
With
ThisWorkbook.VBProject.VBComponents.Item(sht.CodeN ame).CodeModule
.InsertLines 1, "Private Sub Worksheet_Activate()"
.InsertLines 2, ""
.InsertLines 3, "Toolbars.YPBar"
.InsertLines 4, ""
.InsertLines 5, "End Sub"
End With
End Sub

--
???,???????
???,???????

http://www.vba.com.tw/plog/


"Luca" wrote:

Hi,

I have a macro that creates a new worksheet, however when it
does this i
want it to add some code to the worksheet_Activate event of
the new sheet.
Assuming the new worksheet is called "NewYP" and the code i
would like in
activate_Sheet event is

Private Sub Worksheet_Activate()

Toolbars.YPBar

End Sub

How do i go about adding the code ?

All help is appreciated

Luca