Add lines of code to module in design view using VBEIDE (See related posts)

Sometimes you'll need to add code programmatically to your Form or Report Module, for example, when you need to add the same code to all controls of a certain type on a form. When the form has a handful of controls it's no big deal, but when there may be dozens or hundreds of controls it can be daunting. The code below will cycle through all of the controls on your form, determine if the control type has to be updated, check to see if the control already has an event defined, and if not, create the event and append the code. Otherwise it'll print the name of the procedure it didn't modify. Prior to running the code please add the reference "Microsoft Visual Basic for Applications Extensibility 5.3" to your project.

Note: The form specified in strForm below must be in design view prior to running the code in order for this to work.


You can call the procedure using:
AddCodeToControls("Form_myForm", "Msgbox " & chr(34) & "Hello World" & chr(34) , "BeforeUpdate"

' This code was originally written by Juan Soto at AccessExperts.net.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Juan Soto at AccessExperts.net

Public Function AddCodeToControls(strFormName As String, strCode As String, strProcedure As String)
    On Error Resume Next
    Dim VBAEditor As VBIDE.VBE
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim obj As Object
    Dim frm As Form
    Dim ctl As Access.Control
    Dim lngHeaderLine As Long
    
    Set VBAEditor = Application.VBE
    Set VBProj = VBAEditor.ActiveVBProject
    Set VBComp = VBProj.VBComponents(strFormName)
    Set CodeMod = VBComp.CodeModule
    Set frm = Forms(strform)
    For Each ctl In frm.Controls
        If ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox _
            Or ctl.ControlType = acTextBox Then
            'Search if object already has an after update event
            lngHeaderLine = CodeMod.ProcStartLine(ctl.Name & "_" & strProcedure, vbext_pk_Proc)
            If Err > 0 Then
                'Procedure does not exist, create it
                lngHeaderLine = CodeMod.CreateEventProc(strProcedure, ctl.Name)
                CodeMod.InsertLines lngHeaderLine + 1, strCode
            Else
                'Procedure does exist, print name for manual editing later
                Debug.Print ctl.Name & "_" & strProcedure & " Not Modified"
            End If
        End If
    Next ctl

End Function