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