Withevents in Runtime Created Userform

[hatman]

I feel like I am missing something here. I use Class Modules to hook events of runtime created controls in design-time created forms. When I copy the resulting architecture to a new project, I wind up dragging a Form (that may be empty) plus a Class Module into the new file. That's fine, but recently I started wondering if I couldn't move the userform to a runtime architecture. What would that look like? This seems like is should work... but it does not. Any advice?

Private U_Frm As clsRunTime_Form

Sub Runtime_Stuff()

    Set U_Frm = New clsRunTime_Form
    
    VBA.UserForms.Add(U_Frm.Frm.Name).Show
    
    Set U_Frm = Nothing
    
End Sub

Option Explicit

Public Frm As Object
Private WithEvents Okay_Button As MSForms.CommandButton

Private Sub Class_Initialize()

    Const Gap As Integer = 10

    Set Frm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

    Frm.Properties("Caption").Value = "New Form"
    Frm.Properties("Width").Value = 240
    Frm.Properties("Height").Value = 210
    Frm.Properties("StartupPosition").Value = 2
    
    Set Okay_Button = Frm.Designer.Controls.Add("Forms.commandbutton.1")

    Okay_Button.Width = 75
    Okay_Button.Height = 25
    Okay_Button.Top = Frm.Properties("InsideHeight") - Okay_Button.Height - Gap
    Okay_Button.Left = Frm.Properties("InsideWidth") / 2 - Okay_Button.Width / 2
    Okay_Button.Font.Size = 12
    Okay_Button.Caption = "Okay"

End Sub

Private Sub Class_Terminate()

    ThisWorkbook.VBProject.VBComponents.Remove Frm

End Sub

Private Sub Okay_Button_Click()

    MsgBox "Pressed"

End Sub

 

[bobsan42]

I believe that to make it actually work you must also tell the button it has to look for something to run on click. So while creating the button you should also add something like:

Okay_Button.onaction = "Okay_Button_Click"

I know how to do it in access, I will try to find out about excel tomorrow.

 

[bobsan42]

Couldn't sleep . ..
The guy claims this works:
https://stackoverflow.com/questions/51524388/assign-sub-to-dynamically-defined-commandbutton

Will check tomorrow ooops today.

 

[Norie]

hatman

Where/when/how are you associating the button you are creating on the userform you are creating with the sub Okay_Button_Click()?

 

[Kyle123]

I don’t think you can mix ide automation and runtime automation like that. I’d suspect that the subscription to the control can’t be added until the user form is initialised. You’re creating it before the user form is initialised. Personally I’d stick with a blank form and forget about using IDE automation, allowing users to keep their security settings intact.

 

[bobsan42]

Sort of in line with the comment from Kyle123, And because the link with solution from Stack overflow actually works I would suggest the following:
- Create the blank form outside the class - manually or by code.
- To actually make it work the class must represent the code in an actual form code module: add the controls when the form initializes - then the events will work.
- However - to be able to hook the form to the class you must do it from inside the form - e.g. in the Initialize event.
If you create the form by code you must also add (or actually insert some lines in) the code module for this form which will have to do the above upon opening the form.

On the whole - manageable, but I would keep on dragging the UF around.

 

[Kyle123]

I'd still go with a manual form, but you could do something like this (though it has significant drawbacks), depending on your use case:

Option Explicit

Dim Frm As Object
Dim uf As Object
Private WithEvents Okay_Button As MSForms.CommandButton

Private Sub Class_Initialize()

    Const Gap As Integer = 10
    
    Set Frm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

    Frm.Properties("Caption").Value = "New Form"
    Frm.Properties("Width").Value = 240
    Frm.Properties("Height").Value = 210
    Frm.Properties("StartupPosition").Value = 2
    
    VBA.UserForms.Add Frm.Name
    
    
    Set uf = VBA.UserForms(VBA.UserForms.Count - 1)
    
    Set Okay_Button = uf.Controls.Add("Forms.commandbutton.1")

    Okay_Button.Width = 75
    Okay_Button.Height = 25
    Okay_Button.Top = uf.Height - 50 - Okay_Button.Height - Gap
    Okay_Button.Left = uf.Width / 2 - Okay_Button.Width / 2
    Okay_Button.Font.Size = 12
    Okay_Button.Caption = "Okay"

End Sub

Public Property Get UserForm() As Object
    Set UserForm = uf
End Property


Private Sub Class_Terminate()

    ThisWorkbook.VBProject.VBComponents.Remove Frm

End Sub

Private Sub Okay_Button_Click()

    MsgBox "Pressed"

End Sub

Private U_Frm As clsRunTime_Form

Sub Runtime_Stuff()

    Set U_Frm = New clsRunTime_Form
    U_Frm.UserForm.Show
    Set U_Frm = Nothing
    
End Sub

 

[hatman]

Thank you all for taking a look at this. Kyle, Good catch. I see now that I was mistaking the addition of the userform class to the object model with teh instantiation of a new object. In my code, the new object was getting autoiunstantiated at the .Show method, too late for any of the event handler code to have any effect. Yeah, I tend to agree that while this scheme can be made to work, the juice isn;t worth the squeeze. Especially the need for teh user to got into teh Trust Center and allow access to the VBA object model. No, dragging the blank, or minimalistic, userform around from project to project is still the more robust solution.