Programmatically Add Event Procedure

[mbreu996]

Greetings,

I need to know how to add an event procedure to a form programmatically. I have a routine where a user generates a custom form & subforms for data input. I need to add event procedures to some of the subforms on the form that have been created with the code.

This all must be done with VBA and I do not have the option of making the event procedure the typical way because the form is created on the spot with code.

Thanks ahead of time for your help,
-Mark

 

[SOS]

For example - here's how you could add a mouse move event to all controls on the form:

    Dim ctl          As Control
    Dim strParentTag As String
    
    On Error Resume Next
    
    For Each ctl In frmThisForm
        ctl.OnMouseMove = "=HandleMouseMove('" & frmThisForm.Parent.Parent.Name & "', '" & frmThisForm.Parent.Tag & "', '" & ctl.Name & "')"
    Next ctl
   
    Err.Clear

 

[vbaInet]

If you really want to go down this route, here are some ideas.

I would be inclined to create a template module that has all these codes commented (unless it won't compile).

1. You search for the relevant sub-routine/function signature that relates to the control type.
2. Copy that block of code into the new report's module by looping till the end. The End Sub or End Function lines would indicate the end of the function.
3. Rename the signature of the procedure.
4. Compile.

You have to read it line by line.

Here's a link:
http://www.cpearson.com/excel/vbe.aspx

 

[SOS]

You can add form and control events by the method I showed. I haven't figured out how to add a before update event though in this way to handle cancel = true stuff.

 

[vbaInet]

You can add form and control events by the method I showed. I haven't figured out how to add a before update event though in this way to handle cancel = true stuff.

Yeap, that works. My example was more to do with amending the VBA editor programatically. It's useful for when you have long lines of code in a function.

 

[mbreu996]

Thanks for the prompt responses, which are very helpful.

I can see how I would use the method SOS suggests - in my case i would not need to loop but rather refer to a particular subform and use the afterupdate event rather than mouse click. I think I would prefer to insert an actual sub or function as the event procedure rather than spelling out all the code. I noticed you have no sub or end sub declared - does it still work without it?

subformX.afterupdate = "subroutine_or_function_name"
would be how I would tweak it

Thank you also vbaInet. Lol...I do not "want" to go this route but don't see much choice at this point. Thanks for the tips and the link to the website - the "creating an event procedure" section looks very promising. I might have to do a little research to fully understand what you have listed out but this is a great start.

I'll post any specific questions that come up as I try this out - thanks for getting me started!

 

[mbreu996]

Yeap, that works. My example was more to do with amending the VBA editor programatically. It's useful for when you have long lines of code in a function.

I will be adding a significant amount of code so this is very helpful.

 

[ChrisO]

Just as a word of explanation and caution here.

The code given by SOS i.e. =HandleMouseMove blah, blah, blah

Does not add the event procedure HandleMouseMove to the code it adds the event property to the controls OnMouseMove property. In other words it is only the Call to the Function, and it must be a Function, but it is not the Function body itself.

The Function can be added using code but it would be better if the Function pre-existed and not created at run time which is what seems to have been asked for.

The problem is that the code required to add the Function at run time only works in an MDB file and will not work if the project is converted to an MDE file. The reason is that the code will require going into design mode and that can’t be done in an MDE file.

So if we go down the avenue of creating the Functions body of code at run time we will forever restrict the project to being an MDB.