Progmatically add code to worksheet_activate

[Luca]

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


"Luca" <Luca@discussions.microsoft.com> wrote in message
news:A39EF283-67DE-4D1B-BAEF-753E07316821@microsoft.com...
> 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
>

[chijanzen]

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

--
天行健，君å*ä»¥è‡ªå¼·ä¸æ¯
地勢坤，君å*ä»¥åŽšå¾·è¼‰ç‰©

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
>

[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



"chijanzen" <chijanzen@discussions.microsoft.com> wrote in
message
news:0CB9F5C0-64AA-438B-BB71-D1779B5CD302@microsoft.com...
> 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
>
> --
> ???,???????
> ???,???????
>
> 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
>>