create and show a form dynamically 1

[venkman]

So I figured out how to add a custom UserForm in VBA using the VBE objects. I want to know how to show them dynamically also, without having to stop the running macro. Does anyone know how to do this? I thought the UserForm class would have a show method... but apparently it does not.

-Venkman

 

[SQLBI]

Venkman,

The userform class does indeed have a Show and a Hide method.

UserForm1.Show will display the form.
UserForm1.Hide will close it.

Hope this helps.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[venkman]

Leigh,

Thanks for taking the time to respond. Unfortunately, I must disagree with you. The class UserForm1, a module you created in the VBE, does in fact have a show method. However, the class UserForm (no 1 at the end) does not have a show method. In whatever vague sense VBA has inheritence, UserForm1 inherits from UserForm. As an example, in the code below foo() runs fine, but bar() gets an error on the last line at run-time:

Public Sub foo()
   Dim x As UserForm1
   Set x = New UserForm1
   x.Show
End Sub

Public Sub bar()
   Dim x As UserForm
   Set x = New UserForm1
   x.Show
End Sub

Any other ideas?
-Venkman

 

[Bowers74]

The following bit of code will create a form for you dynamically and then open it for you too:

Sub DynamicForm()
Dim NewForm As Object
Set NewForm = ThisWorkbook.VBProject.VBComponents.Add(3)
With NewForm
    .Properties("Caption") = .Name
    .Properties("Height") = 225
    .Properties("Width") = 150
End With
VBA.UserForms.Add(NewForm.Name).Show
End Sub

Be sure to have a reference to MS VBA Extensibility in your project before running this!

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[venkman]

You d'man Mike! I've been pulling my hair out over this for the last day. Thanks!

-Venkman