Programmatically inserting a worksheet change event

[Thebatfink]

Hi,

I'm trying to insert a worksheet change event using VBA. I have this sample code from here -

http://www.cpearson.com/Excel/vbe.aspx

    Sub CreateEventProcedure()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE = """" ' one " character

        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("ThisWorkbook")
        Set CodeMod = VBComp.CodeModule
        
        With CodeMod
            LineNum = .CreateEventProc("Open", "Workbook")
            LineNum = LineNum + 1
            .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
        End With
    End Sub

I want to create the following a change event as below

"Private Sub Worksheet_Change(ByVal Target As Range)"

I presume this is how you would code the Worksheet_Change, but how do I get my "(ByVal Target As Range)" in there??

            LineNum = .CreateEventProc("Change", "Worksheet")

Also, all the other examples look for the last line in the sheets code, and start pumping the code in from the next line by defining LineNum as .CountOfLines + 1.. I have existing code in the sheets I'm trying to place this code into, do I need to worry about its placement and if so, how do I tell it where to place the code. In the example given for inserting a event procedure, LineNum is defined as .CreateEventProc from the start??

Thanks for any help!
Batfink

 

[RoryA]

First, why do you keep resetting VBProj to the same thing?
Second, instead of using the same variable for each vbcomponent, try using different variables and see what happens. I'm a little surprised you aren't getting any errors on your version that uses the sheet's tab names.

 

[Thebatfink]

I had taken out all the resetting, but when it didn't work, I figured that maybe the cause as the original code was not displayed that way..

When you say different variables.. do you mean such as..

Dim VBComp1 As VBIDE.VBComponent
Dim VBComp2 As VBIDE.VBComponent
Dim VBComp3 As VBIDE.VBComponent
Set VBComp1 = VBProj.VBComponents("Module2")
Set VBComp2 = VBProj.VBComponents("Sheet2")
Set VBComp3 = VBProj.VBComponents("Sheet3")

?

Thanks for the help.