Solved Run a class module automatically on every form

[zelarra821]

Buenas tardes. It seems to me that what I am going to ask is a utopia and, therefore, the solution is through the simple path.

The thing is, I have developed a class module to disable all hotkeys. I want this to be executed automatically in each form without having to add lines of code to each form to activate it. I don't know if I explain myself.

I will try to do it graphically.

I have this class module:

Option Compare Database
Option Explicit

Private WithEvents Form As Access.Form
Private mPresionarTecla As Boolean
Private Const acRichtext = 1
Public Sub InitalizeAutokeys(FName As Form, Optional Ptecla As Boolean = True)
    Set Form = FName
    Me.PresionarTecla = Ptecla
    Form.OnKeyDown = "[Event Procedure]"
    Form.KeyPreview = True
End Sub

Public Property Get PresionarTecla() As Boolean
    PresionarTecla = mPresionarTecla
End Property

Public Property Let PresionarTecla(ByVal vNewValue As Boolean)
    mPresionarTecla = vNewValue
End Property

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If Me.PresionarTecla Then
        Select Case KeyCode
            Case vbKeyF1, vbKeyF5, vbKeyF6, vbKeyF9, vbKeyF10, vbKeyF12
                KeyCode = 0
        End Select
        
        Select Case Shift
            Case 2
                Select Case KeyCode
                    Case vbKeyV
                        If Form.ActiveControl.controlType = acTextBox Then
                          Dim txt As TextBox
                          Set txt = Form.ActiveControl
                          If txt.TextFormat = acRichtext Then
                            KeyCode = 0
                            rbPegarSinFormato
                          End If
                        End If
                    Case Else
                        KeyCode = 0
                End Select
        End Select
                
        Select Case KeyCode
            Case 18
                Select Case KeyCode
                    Case Else
                        KeyCode = 0
                End Select
        End Select
    End If
End Sub

Private Sub Class_Terminate()
    Set Form = Nothing
End Sub

To start it in each of the forms, I have to create a variable for the entire form in question, at the beginning of the code, like this:

Dim AutoKeys as New AutoKeys

Then, in the On_Load event, start the class module like this:

AutoKeys.InitalizeAutokeys Me, True

What I want is to avoid having to add those two lines of code in each form. I have databases with many forms and it would be a huge gig.

Thank you so much.

 

[MajP]

I do not think there is a way to avoid writing code, but you can write code to write code using VBA Extensibility.

Programming In The VBA Editor

www.cpearson.com

Then you loop all forms modules and write this code to declare the variable and add to the on_load event.

 

[CJ_London]

If you had it as a standard module with a public function with a form parameter you could just put
=myfunctionname([form])

against the form open event instead of [event procedure]

 

[zelarra821]

I do not think there is a way to avoid writing code, but you can write code to write code using VBA Extensibility.

Programming In The VBA Editor

www.cpearson.com

Then you loop all forms modules and write this code to declare the variable and add to the on_load event.

I'm looking at the page and I can't find how to do what you are doing. Could you show an example that I can apply?

 

[MajP]

If you had it as a standard module with a public function with a form parameter you could just put
=myfunctionname([form])

against the form open event instead of [event procedure

The idea was to avoid writing (even miniscule code) into multiple forms. But that would be easier because you could loop all forms in design view and write that code without extensibility. You can then call a standard module that instantiates the class. Make the class variable a public variable. I would have to try to demo that. This would work easy if only one form ever is open at a time that is "Autokey". If not then you would need a public AutoKeys collection class to manage multiple instances of Autokey. Then I would think writing the code using Extensibility would be easier.

@zelarra821 I will try to demonstrate using extensibility. Will take a little bit of time.

 

[zelarra821]

I will wait, don't worry. Thanks

 

[MajP]

Public Sub LoopFormModules()
Dim VBAEditor As VBIDE.VBE
Dim vbProj As VBIDE.VBProject
Dim vbcomp As VBIDE.VBComponent
Dim vbMod As VBIDE.CodeModule

Set VBAEditor = Application.VBE
'''''''''''''''''''''''''''''''''''''''''''
Set vbProj = VBAEditor.ActiveVBProject

For Each vbcomp In vbProj.VBComponents
  If Left(vbcomp.Name, 5) = "Form_" Then
    Set vbMod = vbcomp.CodeModule
    Debug.Print "Module Name: " & vbcomp.Name
    LoopFormProcedures vbMod
  End If
Next vbcomp

End Sub
Public Sub LoopFormProcedures(vbMod As VBIDE.CodeModule)
          Dim pk                    As VBIDE.vbext_ProcKind
          Dim sProcName             As String
          Dim iCounter              As Long
          Dim oldProcName As String
          Dim jCounter As Long
         ' iCounter = 1
         'should bypass options and
          iCounter = vbMod.CountOfDeclarationLines + 1
          vbMod.InsertLines iCounter, "Private AK as New AutoKeys"
               Do While iCounter < vbMod.CountOfLines  'Loop through each procedure
                   sProcName = vbMod.ProcOfLine(iCounter, pk)
                   'If sProcName <> "" Then
                         If sProcName = "Form_Load" And oldProcName <> sProcName Then
                            Debug.Print "---- Procedure Name: " & sProcName
                            oldProcName = sProcName
                            For jCounter = iCounter To iCounter + vbMod.ProcCountLines(sProcName, vbext_pk_Proc)
                              If vbMod.Lines(jCounter, 1) = "End Sub" Then
                                 vbMod.InsertLines jCounter, "AK.InitalizeAutokeys Me, True"
                                 iCounter = iCounter + 1
                                 Exit For
                               End If
                            Next jCounter
                        End If
                        iCounter = iCounter + 1
               Loop
End Sub

You need to read that Pearson site because almost all of this is there. So this works, but is only the beginning. I can help write the rest, but if you plan to use VBE you need to get familiar because there are a lot of non-intuitive properties and methods. Pearson does a good job of explaing.

This loops the components of the Active project. A component is the container that holds the module itself. Most of the time you loop the components but all work is done with the module. The awkward thing is there is no Procedure Collection or Procedure Object. So you have to read through the module (CodeMod) and you can determine that portion of the module that is a procedure. But to get information about the procedure you work through the CodMod. There are several examples on the internet where people have built custom classes to make it easier to work with Procedures. When you return the "procedure" through the CodeMod the white space before the procedure is also part of the procedure so that is confusing if you want to insert. I find where the procedure starts then start going line by line until I find what I am looking for.

I decided to add the text for instantiation at the bottom of the Form_Load procedure to avoid checking the beginning for variables and constant declaration.

In a module everything before the first procedure is called "Declarations". (Option explict, Enums, Constants, etc). I declare the variable AutoKey after all of this and before the first procedure. Also I would never do this
Private AutoKeys as New AutoKeys
(Never name the variable the same as the class)
instead
Private AK as New Autokeys

For you to try
1. Not all forms have an OnLoad event. You need to write a function to first read through the module to see if a Form_Load procedure exists. If not you can add it. This is clearly shown in the Pearson Site. He adds to the end of the module.
2. You may want to check if the new code already exists so that you do not add it again when you add a new form.
3. You may want to also delete or edit a line of code from all modules
Lets say you insert
Private AutoKeys as New Autokeys
then decide it is better
Private AK as New Autokeys
4. If you add an event procedure through VBE I am not sure if the form "onLoad" property will get the needed "[Event Procedure]" This however can be done by looping the AllForms collection and opening the form in design view, updating the property, and saving the form.

 

[MajP]

Of course this goes without saying. BACKUP before doing anything in VBE. See reference

 

[gemma-the-husky]

A couple of things. When I wrote code to add processes to form modules in order to standardise them, I checked whether the code was already there. If not, I found the appropriate insertion point. Eg, immediately after the form_open line, for instance.

I also found that I could only change a maximum number of forms at a time without causing problems, so I changed 50 forms at a time, then saved the edits.

Note that editing code immediately resets your programme. (It did in my case, anyway)

 

[zelarra821]

That's amazing. Do you think it's worth to do all that only for not to write a couple of lines in each form?

 

[The_Doc_Man]

Unless you have a VERY large number of forms, I would think it would take less time to manually COPY/PASTE a line or two of code for each form's OnOpen or OnLoad event.

If the forms already exist, that may be the best choice. BUT if you are building ALL of this from scratch, just build one form as a template with ALL of your customizations already in place and then copy it and save it to a new form name. The last couple of times I had to do that, it saved me anywhere from 40% to 50% of my typing.

 

[MajP]

Do you think it's worth to do all that only for not to write a couple of lines in each form?

If you plan to develop in VBA the VBE is a great tool to have in your quiver. It may be easier to type it now, but it would be worth the effort in learning. Also if you do not have MZ-Tools that is worth the 63 dollars to make coding easier too. It has lots of features.
VBE is not that hard to work with, but like I said it is a little quirky when working with procedures. Of course I ended up writing some custom classes to help with this. I do not have them with me since I am on travel, but will send if you want.

 

[MajP]

This exchange talks about some of the quirks and the Author has put a lot of code on GitHub to fix

Ugly workaround to get the vbext_ProcKind of a procedure is breaking encapsulation

This is a follow up to Extending the VBAExtensibility Library. It turns out that code had a really nasty bug. Anytime vbeProcedure.StartLine got called, I was running the risk of hitting runtime er...

codereview.stackexchange.com

 

[zelarra821]

I appreciate your effort, @MajP . I think I'm going to use the simple form. Like @The_Doc_Man says, it's better to create a demo form with basic code. That's what I'm doing now: a demo database with basic features that I can use to create a database and save time, not doing from scratch.

 

[CJ_London]

The idea was to avoid writing (even miniscule code) into multiple forms

But you need code to instantiate the class. My suggestion doesn’t require code in the form, in fact you can set the has module property to no

 

[MajP]

My suggestion doesn’t require code in the form, in fact you can set the has module property to no\

It would still require you to edit every form, to put something in the OnLoad property. The assumption is that this is not going to be done manually since there are possibly 10s or hundreds of form. So your way would require looping Allforms opening up every form in design view editing the onload and saving. This is not trivial for a novice, but I agree it would still be easier for most people than using VBE. For me since I have the code to do either it is 6 of one half dozen of another. Although since I understand this class I think a common function will be limiting to what you can do.
However, in your method the code could call a common procedure to instantiate a public AutoKeys, but the Autokeys code cannot be in a standard module since it traps multiple form events.
You could potentially put functions in all of the event procedures, but then that assumes there are no existing procedures in those events.
For sure you cannot set the has module to no since most of these forms (if not all) have lots of other code.