PDA

View Full Version : Programmatically Add Procedure to Sheet Module



benri
06-13-2018, 11:42 AM
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

Bob Phillips
06-13-2018, 01:47 PM
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

Paul_Hossler
06-13-2018, 03:33 PM
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

Bob Phillips
06-14-2018, 03:51 AM
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.

benri
06-14-2018, 11:17 AM
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 (https://drive.google.com/open?id=1icJ7qUQjNwg9VAroCrbjfwEg0JlWsZuu). 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

Bob Phillips
06-14-2018, 03:14 PM
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

benri
06-14-2018, 03:46 PM
xld, thank you. Your solution proved to be the winner in my scenario. :clap: