How To call Worksheet_Change event from a different module

[Nerisha]

Hi.

I want to be able to call the worksheet_Change function from a different module on my excel workbook. I have code working properly when I call it from the actual sheet, but this sheet gets created via code, so I need the "Worksheet_change" event code to be added automatically as well. Can anyone help me?

 

[VoG]

This may help you http://www.cpearson.com/excel/VBE.aspx

 

[Nerisha]

Thanks. I have just been looking at that site, but don't understand what to do. I'm very new to all of this. Can you please explain it a bit better.

 

[VoG]

This adds a simple Worksheet_Change procedure to Sheet2

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("Sheet2")
        Set CodeMod = VBComp.CodeModule
        
        With CodeMod
            LineNum = .CreateEventProc("Change", "Worksheet")
            LineNum = LineNum + 1
            .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
        End With
    End Sub

 

[Nerisha]

I've tried out your code. It breaks on this line:
Dim VBProj As VBIDE.VBProject
Saying : "User-defined type not defined"

 

[VoG]

From the link that I gave

First, you need to set an reference to the VBA Extensibililty library. The library contains the definitions of the objects that make up the VBProject. In the VBA editor, go the the Tools menu and choose References. In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3. If you do not set this reference, you will receive a User-defined type not defined compiler error.

 

[Nerisha]

Ok, I realize it was a reference missing. Got it now. Your code inserts the lines into the Worksheet_Change event. How would I add the following lines to my code:

Dim conn As Object 'ADODB.Connection
Dim rs As Object ' ADODB.Recordset
Dim ozConnStr As String
Dim sql As String
Dim Amt As Long
Dim Item As String

If Target = Range("B22") Then
Item = Range("B22")

Set cn = ozConnection()
Set rs = New ADODB.Recordset

sql = "SELECT AmountExcl, ItemDescr FROM Items WHERE ItemDescr = " & ToSql(Item) & ""
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, conn

If Not rs.EOF Then
Amt = rs!AmountExcl
End If

Range("C22") = Amt
End If

Thanks

 

[VoG]

You need to insert one line of code at a time, like this

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("Sheet2")
        Set CodeMod = VBComp.CodeModule
        
        With CodeMod
            LineNum = .CreateEventProc("Change", "Worksheet")
            LineNum = LineNum + 1
            .InsertLines LineNum, "Dim conn As Object 'ADODB.Connection"
            LineNum = LineNum + 1
            .InsertLines , LineNum, "Dim rs As Object ' ADODB.Recordset"
            '
            'and so on
            '
        End With
    End Sub

 

[Nerisha]

Thanks, I'm just struggling with this line here:
sql = "SELECT AmountExcl, ItemDescr FROM Items WHERE ItemDescr = " & " & Item & " & "

what I need is the result to look like this :
SELECT AmountExcl, ItemDescr FROM ITems WHERE ItemDescr = "Couch"

How do I get this statement to get the query result I need?

 

[VoG]

Try

.InsertLines LineNum, "Sql =" & DQUOTE & "SELECT AmountExcl, ItemDescr FROM Items WHERE ItemDescr = " & ToSql(Item) & "" & DQUOTE