UserForm.Show method not supported? Created UserForm using code

[pebcak]

I've created a Userform programmatically (I know ... a lot of work, but I have my reasons). Excel trusts access to VBA project object model, and the Microsoft Visual Basic for Applications Extensibility 5.3 reference is selected.


Dim ufPld As VBComponent
Set ufPld = wbDB.VBProject.VBComponents.Add(3)


The form comes out great - the code loops through the data source, places all the controls in the right spots, fills in all of the captions properly... except I can't display it! The form is created and exists within the project but


ufPld.Show

throws an "Object doesn't support this property or method" error.

Please help? :(

[Dave]

From Google... dynamic creation of userform (microsoft.com) (https://social.msdn.microsoft.com/Forums/en-US/70544b06-5f87-4f11-83bd-967c909a9b20/dynamic-creation-of-userform?msclkid=3f13041fcf3411ec8ca5d4b78917c1d8)

Dim MyUserform As VBComponent
Set MyUserform = ThisWorkbook.VBProject.VBComponents.Add(3)
VBA.UserForms.Add(MyUserform.Name).Show
HTH. Dave

[Paul_Hossler]

It's a two step process

1. Add UF to Project

2. Add UF to UserForms collection to .Show

FWIW, I've found it more reliable to use the item number in the UserForms collection




Option Explicit


Sub test()
Dim ufPld As VBIDE.vbcomponent


'add to project
Set ufPld = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)


'add to userforms collection
Debug.Print VBA.UserForms.Count
VBA.UserForms.Add ufPld.Name
Debug.Print VBA.UserForms.Count

'show using item number
VBA.UserForms(VBA.UserForms.Count - 1).Show ' Zero based so need to subtract 1


End Sub

[pebcak]

Thank you so much!! Was driving me nuts, had the objects mixed up.

I think? Now that it works it's gonna take me another week to figure out why, haha. Anyway, thanks to both you and Paul.

[Dave]

You are welcome and I'm guessing that Paul also appreciates when outcomes are posted. Have a nice day. Dave