Consulting

Results 1 to 7 of 7

Thread: Programmatically Add Procedure to Sheet Module

  1. #1
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    3
    Location

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Something along the lines of

    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by benri View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    3
    Location
    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

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I am saying don't use worksheet change event code in each worksheet, instead use one workbook sheetchange event in ThisWorkbook

    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    3
    Location
    xld, thank you. Your solution proved to be the winner in my scenario.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •