Programmatically Add Procedure to Sheet Module

Hello everyone,

I am completely stumped on a solution and looking for some guidance.

I have written code that among other things, adds a new sheet to a workbook. The workbook in question has a dropdown via data validation, which requires a worksheet change handler in the sheet module. I have the needed code for the event change, but I am unable to find a way to programmatically add the procedure to the sheet module.

Is it possible to simply activate/select a specific sheet and add the code via VBA? I've searched multiple threads and the solution eludes me.

-B

Something along the lines of

Code:

---

public  Sub CreateEventProcedure()
Dim VBProj As Object 'VBIDE.VBProject
Dim VBComp As Object 'VBIDE.VBComponent
Dim CodeMod As Object 'VBIDE.CodeModule
Dim LineNum As Long
Const SHEET_NAME As String = "Sheet1"
Const EVENT_PROCEDURE As String = "Change"
Const SQL_CODE_LINE1 As String = "If Not Intersect(Target, Me.Range(""C8"")) Is Nothing Then "
Const SQL_CODE_LINE2 As String = ""
Const SQL_CODE_LINE3 As String = "    MsgBox ""Hello World"""
Const SQL_CODE_LINe4 As String = "End If"
   
    If VBAIsTrusted(ActiveWorkbook.VBProject) Then
   
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents(SHEET_NAME)
        Set CodeMod = VBComp.CodeModule
       
        With CodeMod
            LineNum = .CreateEventProc(EVENT_PROCEDURE, "Worksheet")
            LineNum = LineNum + 1
            .InsertLines LineNum, SQL_CODE_LINE1
            LineNum = LineNum + 1
            .InsertLines LineNum, SQL_CODE_LINE2
            LineNum = LineNum + 1
            .InsertLines LineNum, SQL_CODE_LINE3
            LineNum = LineNum + 1
            .InsertLines LineNum, SQL_CODE_LINe4
        End With
    End If
End Sub

'-----------------------------------------------------------------
Private Function VBAIsTrusted(ByRef Project As Object) As Boolean
'-----------------------------------------------------------------
Dim mpVBC As Object
Dim mpAlerts As Boolean

    mpAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Set mpVBC = Project.VBComponents.Item(1)
    On Error GoTo 0
    Application.DisplayAlerts = mpAlerts
    VBAIsTrusted = Not mpVBC Is Nothing
End Function

---

Quote:

---

Originally Posted by benri

Hello everyone,

I am completely stumped on a solution and looking for some guidance.

I have written code that among other things, adds a new sheet to a workbook. The workbook in question has a dropdown via data validation, which requires a worksheet change handler in the sheet module. I have the needed code for the event change, but I am unable to find a way to programmatically add the procedure to the sheet module.

Is it possible to simply activate/select a specific sheet and add the code via VBA? I've searched multiple threads and the solution eludes me.

-B

---

What I like to do (since IMHO it's easier) is to create a VeryHidden template worksheet (e.g. "_UserData") with the event handlers, formats, etc. just the way I need

Then instead of just Worksheets.Add, I make _UserData visible, copy it as "UserData", and then make the template VeryHidden again

Another way is to have workbook sheet events that can manage all of your sheets. You would need some sort of naming convention in your sheets so that it would also handle new sheets, but it would be much simpler and more manageable.

Thanks to all of you who have replied with suggestions. I'm very new to VBA and looking for the simplest solution that is also effective. I've read threads re: copying hidden sheets and templates, however I am unclear on how to accomplish this. Currently all the modules reside in my personal VBA project. It sounds like I could create a template there and copy it to the newly created sheet. However, I don't want the copy to interfere with the data thats being exported to the new sheet. I have uploaded all the code I have written thus far, which can be found here. I'm not sure if seeing this would help with further guidance. The event handler I need on the newly added sheet is at the very bottom of the text file.

Again, thank you all for helping a noob, it's truly appreciated.

-B

I am saying don't use worksheet change event code in each worksheet, instead use one workbook sheetchange event in ThisWorkbook

Code:

---

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   
    'this is to be inserted into the AllEvents (sheet2) module
    'this code allows the data validation dropdown to function
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address <> "$A$1" Then Exit Sub
    Application.EnableEvents = False
    If Me.FilterMode Then Me.ShowAllData
   
    Select Case Target.Value
        Case "All Events"
        Case "No Mains & Ends"
            ActiveSheet.Range("$A$2:$O$1494").AutoFilter Field:=6, Criteria1:="<>Main and Ends"
        Case "Sub Decisions"
            ActiveSheet.Range("$A$2:$O$1494").AutoFilter Field:=4, Criteria1:="Subtitle"
    End Select
   
    Application.EnableEvents = True

End Sub

---

xld, thank you. Your solution proved to be the winner in my scenario. :clap: