click event not working on programmatically / dyna

问题:

I have the following code that programmatically / dynamically creates a frame and adds an option button:

Private Sub ComboBox1_Change()

    Dim cb1234Frame As MsForms.Frame
    Dim opbtn1 As MsForms.OptionButton

    Set cb1234Frame = RT_Graph_Form.Controls.Add("Forms.Frame.1")

    With cb1234Frame
        .Top = 132
        .Left = 12
        .Height = 30
        .Width = 144
        .Caption = "Number of Graphs to Display"
    End With

    Set opbtn1 = cb1234Frame.Controls.Add("Forms.OptionButton.1")

    With opbtn1
        .Top = 6
        .Left = 6
        .Height = 18
        .Width = 21.75
        .Caption = "1"
    End With

End Sub

But then this does not work:

Private Sub opbtn1_Click()

    MsgBox "Test Successful!!"

End Sub

回答1:

The problem is that event handlers need to be bound at compile-time: you cannot create an event handler for a dynamically created control.

Add a new class module to your project, call it DynamicOptionButton. The role of this class is to wrap the MSForms control and have a compile-time reference to it:

Option Explicit
Private WithEvents Wrapper As MSForms.OptionButton

Public Sub Initialize(ByVal ctrl As MSForms.OptionButton)
    Set Wrapper = ctrl
End Sub

Private Sub Wrapper_Click()
    MsgBox "Works!"
End Sub

Note that only a subset of the events will be available to handle: what events are available, depend on the interface you're declaring the wrapper reference with - MSForms.Control has a number of events (and properties), MSForms.OptionButton has another set: you may need to declare both interfaces (i.e. 2 wrappers for the same object) in order to access all the members.

Now in your form's declarations section, you'll need to hold a reference to all wrappers, otherwise the objects just fall out of scope and the handlers won't work. A Collection can do that:

Option Explicit
Private ControlWrappers As Collection

Private Sub UserForm_Initialize()
    Set ControlWrappers = New Collection
End Sub

'...

Private Sub CreateOptionButton()
    Dim ctrl As MSForms.OptionButton
    Set ctrl = Me.Controls.Add("Forms.OptionButton.1")
    'set properties...

    Dim wrap As DynamicOptionButton
    Set wrap = New DynamicOptionButton
    wrap.Initialize ctrl

    ControlWrappers.Add wrap
End Sub

Be careful to never reference the form's class name in the form's own code-behind: the global-scope RT_Graph_Form identifier refers to a VBA-controlled "default instance" auto-instantiated object that may or may not be the actual form instance that's being shown. You want to add your dynamic controls to Me.Controls, not RT_Graph_Form.Controls.

Now, we can handle events of controls spawned at run-time, but there's another problem: the event handler in the DynamicOptionButton class has no reference to the form it's on!

Or does it?

Every MSForms control has a Parent property; you can get ahold of the parent UserForm by recursively going up the Parent property until the returned reference is a UserForm - and from there you can access everything that's publicly exposed.

回答2:

I'm not sure it's appropriate, but I managed to do-ish it.

I'm creating the userform from thisworkbook direcly so everything is stored there. I did not need the parent property anywhere.

Option Explicit

Const FolderPath As String = "C:"

Public TESTS As New Collection
Public CONTROLWRAPPERS As New Collection

Sub gotothere()
On Error GoTo bleh
Call Shell("explorer.exe" & " " & FolderPath & "\" & ThisWorkbook.ActiveSheet.Range("C14").Value, vbNormalFocus)

Exit Sub
bleh: Call Shell("explorer.exe" & " " & FolderPath, vbNormalFocus)
End Sub

Sub ChooseFolder()
Call Createform
End Sub

Private Sub Createform()

Set TESTS = Nothing

Call listalltests
Call Module1.MakeUserForm

Dim i As Integer

For i = 1 To TESTS.Count
   Call CreateCommandbuttonButton(i)
Next i

Formol.Show vbModeless
End Sub

Private Sub listalltests()

Dim objFSO As Object
Dim objFolder As Object
Dim objSubFolder As Object
Dim i As Integer
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FolderPath & "\")
i = 1
For Each objSubFolder In objFolder.subfolders
TESTS.Add objSubFolder.Name
i = i + 1
Next objSubFolder
End Sub

Private Sub CreateCommandbuttonButton(pos As Integer)
    Dim ctrl As MSForms.CommandButton
    Set ctrl = Formol.Controls.Add("Forms.commandbutton.1")
    With ctrl
        .Caption = TESTS(pos)

        If (pos * 20 + 2) > 600 Then
            .Left = 130
            .Top = (pos - 29) * 26 + 2
            .Width = 102
        Else
            .Left = 12
            .Top = pos * 26 + 2
            .Width = 102
        End If
    End With

    Dim wrap As DynamicOptionButton
    Set wrap = New DynamicOptionButton
    wrap.Initialize ctrl
    CONTROLWRAPPERS.Add wrap
End Sub

The MakeUserForm function is stored in a module and just check if there is a form named formol and if not create it with a certain width & height. it's an empty form.

The class is the exact same as the one made by mathieu except for the Wrapper_click event.