Home » Blogs » Vishesh's blog

Simple way to add Event Proc skeleton in a sheet module

Vishesh's picture
Submitted by Vishesh on 1 July, 2011 - 17:08
'Here is the code to add an Activate event to the sheet module. 
Sub CreateEventProcedure(wks As Worksheet, strEvtProc As String)
    With ActiveWorkbook.VBProject.VBComponents(wks.CodeName).CodeModule
        'Add the blank procedure
        .CreateEventProc strEvtProc, "Worksheet"
    End With
End Sub
 
Sub TestProc()
    Call CreateEventProcedure(ThisWorkbook.Worksheets("Sheet2"), "Activate")
    MsgBox "Goto Sheet2 module in the code window. The activate procedure skeleton is added.", vbInformation, "Excel Experts"
End Sub
»

The procedure does not get

Submitted by Anonymous on 3 July, 2011 - 02:29.
The procedure does not get added: 
Sub CreateEventProcedure(wks As Worksheet, strEvtProc As String)
With ActiveWorkbook.VBProject.VBComponents(wks.CodeName).CodeModule
'Add the blank procedure
For i = 1 To 100
i = i + 1
Next
.CreateEventProc strEvtProc, "Worksheet"
End With
End Sub
Still creates only the skeleton in sheet 2: 
Private Sub Worksheet_Activate()
End Sub
Maybe I misunderstood the meaning of "blank procedure"
»
Vishesh's picture

This is what it is supposed

Submitted by Vishesh on 4 July, 2011 - 14:32.

This is what it is supposed to do...add only the Event procedures.

»