Progmatically add code to worksheet_activate

[Guest]

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

 

[Chip Pearson]

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

 

[Guest]

Luca:

try,

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

 

[Chip Pearson]

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



message