1

available event procedures for userform controls

Hi! I searched google already but found only 1 page mentioning how to do it in MS Access but not in MS Excel, here: List an MS Access form's controls with their events.

I know how to list the controls on a userform.
What I want to know is how to get to the list of events available to a control in the code editor (just clarifying).

Is there a way to programmatically list all available event procedures for a control on a userform like a command button, so that I can add that list of events to an array/collection/dictionary for other uses.
I can't find the control.properties as mentioned the referred link above since I am working in Excel, where this property is not exposed, I guess.

I wish I could go through a collection of properties/events like "for each oneEvent in oneControl.Events" and I know that it is not possible.
I think there must be an internal list/collection like that because we can go through Object Browser in VBA Editor or write event handler code in the VBA Editor.
Is there a way to access that list/collection (may be through VBIDE.VBProject)?

Many thanks in advance.

Nay Lynn
  • 351
  • 1
  • 12
  • Maybe look here: https://stackoverflow.com/questions/1892039/iterating-through-the-object-browser-in-vba or https://stackoverflow.com/questions/16193458/is-there-a-way-to-count-elements-in-a-vba-enum – Tim Williams Jan 30 '21 at 06:57
  • ...or https://learn.microsoft.com/en-us/archive/msdn-magazine/2000/december/visual-basic-inspect-com-components-using-the-typelib-information-object-library – Tim Williams Jan 30 '21 at 07:04
  • @Tim Williams, many thanks. The typelib is very advanced for me as I'm starting to learn my way around codemodule programming and such. Still, after reading through the links that you provided, I found many interesting code snippets which show details of controls that I didn't know existed. These code snippets list various properties/sub/functions of controls but none shows how to list events available to a control. I believe I will find how eventually after reading more and that this is a good start on the right track because of your kind direction. I will keep on exploring TypeLib.Thank you. – Nay Lynn Jan 30 '21 at 09:19

1 Answers1

3

Hurrah!
I finally found the answer myself! Well, with a little help from @Tim Williams.
It took me 5 days to research the topic of TLI.

Even when my country, Myanmar, was under siege by an unlawful and awful military coup (on 01FEB2021), I couldn't stop thinking about this TLI issue I am facing, despite having to live through anguish, anger, pain, uncertainty and the feeling of being violated.
Now, I got it solved.

That said, I still don't really understand how the whole TLI thing works.
I think it would take me several years of reading on the subject matter to fully understand its declarations, mechanism, functions and methods.
Anyway, I will share what I found out through reading a lot of webpages and finally getting to the answer using a simple watch window to figure out how to get to the list of event procedure names that are available to a given userform control in VBA.
The following sub was taken from another stackoverflow page about listing the properties of userform object, I got stuck with it because I don't fully understand how the structure of the return data is formatted but that was overcome by looking at the structure using the Watch window in VBA Editor.

Requirement: Reference to TypeLib Information library at C:\Windows\SysWow64\TLBINF32.DLL

Sub listControlEventNames(ByVal o As Object)

Dim t As TLI.TLIApplication
    Set t = New TLI.TLIApplication

Dim ti As TLI.TypeInfo
    Set ti = t.ClassInfoFromObject(o)
    
Dim i As Integer
Dim mi As TLI.MemberInfo
    For Each mi In ti.DefaultEventInterface.Members
        i = i + 1
        ActiveSheet.Cells(i, 1).value = mi.Name
    Next
End Sub

This sub should be called with a userform control name like:

call listControlEventNames(UserForm1.ToggleButton1)

or

listControlEventNames Userform1.ToggleButton1

Finally, I can swear to God (but I'm a Free Thinker) that I can't really find how to list UserForm Control Events anywhere on the web, let alone a user manual on this library. The many many example code snippets, explanations and discussions that I found on TLI, were, for listing properties of UserForm Controls only.
Anyway, enjoy!

sub output

Nay Lynn
  • 351
  • 1
  • 12
  • Very cool, out of curiosity why did you want this? This is the kind of stuff available in [`stdCOM`](https://github.com/sancarn/stdVBA/blob/master/src/stdCOM.cls) (using `InterfaceInfo` property) although I didn't know there was a default event interface for `IDispatch`?! That is very cool! – Sancarn Aug 28 '21 at 10:25
  • 1
    Hi,@Sancarn, my github handle is NLYinMaung & my Reddit, blasphemorrhoea.We met and we talked before on your shapeEvents. So honoured to get a comment from you. – Nay Lynn Aug 28 '21 at 13:39
  • Why I want this?1.To filter out default Event handlers from User-created functions for my project at https://github.com/NLYinMaung/VBA-Project-Information. 2.To list as well as to be able to show, in real time, which control was clicked on the userform/designer-window in VBE while my VBAProjectInfo Userform is running as well as indicate it in a list as https://raw.githubusercontent.com/NLYinMaung/VBA-Project-Information/main/images/vbproject%20info%20clicked.jpg, as control name and it's event handler name, in bottom 2 lists. – Nay Lynn Aug 28 '21 at 13:41
  • A gif showing the real time clicked event result is shown here:https://stackoverflow.com/questions/67071411/treeview-vs-listview-mousedown-long-press-event-sequence-code-execution-pause/67071412#67071412 – Nay Lynn Aug 28 '21 at 13:41
  • I remember that I still can't solve Activate, Initialize and Resize events of the UserForm. I think the reason lies in the fact the VBA userform is a ThunderDFrame inside an ActiveX container window or something. And those 3 events (may be more as I can't remember well) were hardcoded into my dictionary and function_name-control_name_match&check function. They were not found in TLI. The VBA userform is still a mystery to me. Where does VBA store those default function names or how does it know/reference them?Those 3 event handlers are-provided-by/are-properties of ActiveX container? – Nay Lynn Aug 28 '21 at 13:51
  • Aha! I knew there had to be a good reason, as getting the names of events isn't useful for much else. So user defined VBA events arent defined in `DefaultEventInterface`? Today I've been going through the [UserForm IDL](https://github.com/sancarn/VBA-STD-Lib-Inspiration/blob/master/_IDL/FM20.IDL) I wondered whether `Resize` event might actually be the [`Layout` event](https://github.com/sancarn/VBA-STD-Lib-Inspiration/blob/master/_IDL/FM20.IDL#L1009-L1010). But I'm not sure. Would make some sense that activate and initialise were built in as these are events starting outside the form. – Sancarn Aug 28 '21 at 20:54
  • Scratch that, didn't realise Layout event is on userforms already in VBA window. My bad. Yeah it is really unfortunate that resize event isn't in there as it's not in the `UserForm` class either. Instead it can only be found in named userform components... – Sancarn Aug 28 '21 at 20:57