Create Userform on the Fly?

PA HS Teacher · Jul 18, 2006

Is it possible to create a userform on the fly?

I know how to create controls on the fly.
e.g.
Set ctl = MyForm.Controls.Add("forms.CommandButton.1")

But what if I wanted to create MyForm on the fly?

mortgageman · Jul 18, 2006

I tried this, but wasn't able to get it right. Maybe you can do better.

Gene, "The Mortgage Man", Klein

tusharm · Jul 18, 2006

Maybe, you can customize a skeleton UF that is already in the add-in...

Is it possible to create a userform on the fly?

I know how to create controls on the fly.
e.g.
Set ctl = MyForm.Controls.Add("forms.CommandButton.1")

But what if I wanted to create MyForm on the fly?

PA HS Teacher · Jul 18, 2006

Gene,
I've take a look at j-walk's site. I also found this Dave Hawley Post.
http://www.ozgrid.com/forum/showthread.php?t=16809&highlight=userform+fly

All of the suggestions I've read appear to require access to the VBA project during runtime. I generally don't think this is a good idea, and I always keep it locked.

I think I will take up Tusharm's suggestion of making generic userforms ahead of time. I think I can just make more forms than I anticipate needing at runtime.

Legacy 98055 · Jul 18, 2006

Another way that will not demand that security settings be lowered (Trust Access to Visual Basic Project must be checked/enabled for JWalks method to work) is to simply create a template userform and then create an instance of it. If you have very similiar forms, you can use this method to combine them into one or to combine events for multiple forms.

TemplateUserform.zip

Create a new workbook.
Create a userform named, "MyTemplateForm".
Placew this code in some class module such as ThisWorkbook or a Worksheet class...

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Public WithEvents uf As UserForm
 Private mtf As MyTemplateForm

 Sub Example()
 Set mtf = New MyTemplateForm
 Set uf = mtf
 Dim l As MSForms.Label

 mtf.Caption = "Instance of MyTemplateForm"
 Set l = mtf.Controls.Add("Forms.Label.1", "Description", True)
 l.Caption = "This is a simple blank form. I can do whatever " & _
 "I wish via code... Click the form!"
 l.AutoSize = True
 mtf.Show
 Unload mtf
 Set mtf = Nothing
 Set uf = Nothing
 End Sub

 Private Sub uf_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 With mtf.Controls("Description")
 .Left = X
 .Top = Y
 End With
 End Sub


 Private Sub uf_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 mtf.Caption = X & ", " & Y
 End Sub
</td></tr></table>

TemplateUserform.zip

tusharm · Jul 18, 2006

{snip}
<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Public WithEvents uf As UserForm
Private mtf As MyTemplateForm
{snip}</td></tr></table>
{snip}

That's an interesting way to solve the problem of userform not exporting critical methods such as Show [Of course, I am not sure I understand why it doesn't but that's another story. {grin}] and MyTemplateForm not having any events attached to it [which is yet another story.]

Legacy 98055 · Jul 19, 2006

Tusharm. Why does this not work? The custom Initialize event is not fired...

Template code.

Code:

Option Explicit

Event Initialize()

Private Sub UserForm_Initialize()
    RaiseEvent Initialize
End Sub

In class module.

Code:

Option Explicit

Private WithEvents uf As UserForm
Private WithEvents mtf As MyTemplateForm

Sub Example()
    Set mtf = New MyTemplateForm
    Set uf = mtf
    Dim l As MSForms.Label
    
    mtf.Caption = "Instance of MyTemplateForm"
    Set l = mtf.Controls.Add("Forms.Label.1", "Description", True)
        l.Caption = "This is a simple blank form.  I can do whatever " & _
                   "I wish via code...  Click the form!"
        l.AutoSize = True
    mtf.Show
    Unload mtf
    Set mtf = Nothing
    Set uf = Nothing
End Sub

Private Sub mtf_Initialize()

End Sub

Private Sub uf_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    With mtf.Controls("Description")
        .Left = X
        .Top = Y
    End With
End Sub


Private Sub uf_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    mtf.Caption = X & ",  " & Y
End Sub

Legacy 98055 · Jul 19, 2006

Nevermind. When I renamed the event, it fires...

Template code:

Code:

Option Explicit

Public Event Initialize2()

Private Sub UserForm_Initialize()
    RaiseEvent Initialize2
End Sub

Code in sheet1 class:

Code:

Option Explicit

Private WithEvents uf As UserForm
Private WithEvents mtf As MyTemplateForm

Sub Example()
    Set mtf = New MyTemplateForm
    Set uf = mtf
    Dim l As MSForms.Label
    
    mtf.Caption = "Instance of MyTemplateForm"
    Set l = mtf.Controls.Add("Forms.Label.1", "Description", True)
        l.Caption = "This is a simple blank form.  I can do whatever " & _
                   "I wish via code...  Click the form!"
        l.AutoSize = True
    mtf.Show
    Unload mtf
    Set mtf = Nothing
    Set uf = Nothing
End Sub

Private Sub mtf_Initialize2()
Stop
End Sub

Private Sub uf_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    With mtf.Controls("Description")
        .Left = X
        .Top = Y
    End With
End Sub


Private Sub uf_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    mtf.Caption = X & ",  " & Y
End Sub

PA HS Teacher · Jul 19, 2006

Right Click,
Thank you for your post. It looks like exactly what I want, and I appreciate you posting an example file, but I'm not quite sure how to create more than one instance at runtime. I don't understand why your code does not have its own class module, and I"m not sure how to create multiple instances.

Background:
I am working on a Lesson Planning system, basically a database. I've created my own objects using class modules to help me manage my data. (Forgive me if my language is sloppy. I'm relatively new at creating my own objects) I've also created some collections of my objects.

My "highest" object successfuly contains the following "properties." I've had success manipulating them and adding them to a collection.
- Range Objects (Cells)
- A collection of Range objects
- Regular Variable types, integer, string, etc.
- Controls, TextBoxes, Command Buttons etc. on an existing form

Question(s):
1. Is it possible to have a Userform as part of this object?
2. Can these forms be loaded and left in the background simultaneously?
3. Can I collect up my records of my new object, including the UF propery into a Collection.

I anticipate something on the order of 10 records/objects, and hence around 10 forms. I will not know exactly how many records until runtime.

Tom Urtis · Jul 19, 2006

Is it possible to create a userform on the fly?

I am in the process of developing my web site, and in the mean time have a very modest temporary single web page.

On it is a downloadable example at the bottom that is a userform being created programmatically, in this example for the purpose of entering an unambiguous date.

It also includes a verification of, and message box instructions for, the user's trusted source setting to be established, because this code writes to the VBE.

Also on the page is a photo of some guy with a face made for radio.

http://www.atlaspm.com/pages/1/index.htm

Create Userform on the Fly?

PA HS Teacher

Well-known Member

Excel Facts

mortgageman

Well-known Member

tusharm

MrExcel MVP

PA HS Teacher

Well-known Member

Legacy 98055

Guest

tusharm

MrExcel MVP

Legacy 98055

Guest

Legacy 98055

Guest

PA HS Teacher

Well-known Member

Tom Urtis

MrExcel MVP

Similar threads

Forum statistics

Share this page

Create Userform on the Fly?

PA HS Teacher

Well-known Member

Excel Facts

mortgageman

Well-known Member

tusharm

MrExcel MVP

PA HS Teacher

Well-known Member

Legacy 98055

Guest

tusharm

MrExcel MVP

Legacy 98055

Guest

Legacy 98055

Guest

PA HS Teacher

Well-known Member

Tom Urtis

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock