Programmatically Add Procedure to Sheet Module

[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

 

[Norie]

Couldn't you use the workbook level SheetChange event?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

This event will be triggered when there's a change in any worksheet in the workbook, and you can refer to the sheet the change was made on using Sh and the range that was changed using Target.

 

[shg]

If you copy a sheet from a template sheet, the code comes with it.

 

[benri]

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

 

[John_w]

I would use Norie's solution. Having looked at your code, the event handler should be something like:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Sh.Name <> "AllEvents" Then Exit Sub
    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

Also, in your case you probably don't want to trigger the Workbook_SheetChange when your other code changes the "AllEvents" sheet (only when a user changes it). If so, disable events (Application.EnableEvents = False) at the places in the code where it changes the "AllEvents" sheet, for example this section of code, I think:

Application.EnableEvents = False

        ' activate the All Events sheet
        Sheets("AllEvents").Select
        
        ' insert row
        Range("A1").EntireRow.Insert
        
        ' adjust row height
        Rows(1).RowHeight = 17
        
        ' data validation for dropdown list
        Range("$A$1").Validation.Add Type:=xlValidateList, Formula1:="All Events,No Mains & Ends,Sub Decisions"
        
        ' add text adjacent to the dropdown
        [B1].Value = " <-- Choose View From Dropdown List"

Application.EnableEvents = True

 

[benri]

Hi John,

Thank you so much for taking the time to look at my code and respond with a solution.

I feel like it's so close, but unfortunately, selecting items in the dropdown is not performing the expected actions.

Though, it may be important to mention that I received no errors from the VBE while executing the code in it's entirety.

What I did was enter the suggested code at the end of module 3 as a new sub. Was that the correct place to put it? I suspect not since viewing the code on the "AllEvents" sheet and there is nothing. I assumed the suggested code would insert the change event on that particular sheet module.

I know I'm doing something wrong, but not sure what it is.

 

[John_w]

The Workbook_SheetChange code should go in the ThisWorkbook module.

 

[benri]

Thanks to all, I finally got it working!