Add lines of code to module in design view using VBEIDE

AccessExpert (Juan Soto) — Mon, 28 Apr 2008 01:38:04 GMT

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

DZone Snippets: vba code

Add lines of code to module in design view using VBEIDE