Progmatically add code to worksheet_activate

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Back
Top