Excel VBA-以编程方式列出用户窗体上控件的可用事件过程 [英] Excel VBA - programmatically list available event procedures for controls on a userform

问题描述

嗨！我已经搜索过google，但是只找到1页提到如何在MS Access中进行操作，而在MS Excel中却没有，这里:

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.

解决方案

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!

这篇关于Excel VBA-以编程方式列出用户窗体上控件的可用事件过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！