Create UserForm programmatically

[Ber]

An Excel worksheet contains a list of names.
When the user selects one of these names, a userform pops up. This Userform
has the same name as the name that is selected in the worksheet.
What I want to achieve is that whenever a name is selected for which there
does not excist a UserForm yet, this form will be created programmatically.
My question is: How can I generate and name a userform in Excel and furnish
it with a textbox..
I have tried Google Groups to no avail.
A search of the Microsoft Knowledge Base came up with the answer, all the
details and sample routines but ..... for Word2000 only - see
http://tinyurl.com/79tn2 .
Your help will be appreciated
Ber


















http://tinyurl.com/79tn2

 

[Jon]

Basically, unless there is a difference in layout for the Userforms for
different names, I would have only one Userform for all of them that sets
the chosen name as the Userform Caption Property in Userform Initialize.

Jon

 

[Ber]

John, thanks for your contribution.
The problem is that my client wants a type of scratch pad for each of his
suppliers.
After scratching his pad the contents have to be saved and this is how the
idea of having a user form for each contact was born.
I am sure there are other, less complicated, solutions to this problem but I
am persisting because I definitely want to know how to create a userform
programatically!!!!
Regards,
Ber
===============================================

 

[Jim Cone]

Ber,
Borrowing liberally from John Walkenbach's web site...
http://j-walk.com/ss/excel/tips/tip76.htm
'------------------
Sub MakeOne()
Dim frmNew As Object
Set frmNew = ThisWorkbook.VBProject.VBComponents.Add(3)
frmNew.Properties("Caption") = " Test Form"
VBA.UserForms.Add(frmNew.Name).Show
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=frmNew
Set frmNew = Nothing
End Sub
'--------------------------
Jim Cone
San Francisco, USA



"Ber" <[email protected]>
wrote in message
An Excel worksheet contains a list of names.
When the user selects one of these names, a userform pops up. This Userform
has the same name as the name that is selected in the worksheet.
What I want to achieve is that whenever a name is selected for which there
does not excist a UserForm yet, this form will be created programmatically.
My question is: How can I generate and name a userform in Excel and furnish
it with a textbox..
I have tried Google Groups to no avail.
A search of the Microsoft Knowledge Base came up with the answer, all the
details and sample routines but ..... for Word2000 only - see
http://tinyurl.com/79tn2 .
Your help will be appreciated
Ber

 

[Jon]

Just to be persistent, any information from any instance of a userform could
be saved independently. Then the userform could be relaunched with different
initialization values for each customer.
I definitely understand the urge to learn new methods. Especially if you can
visualize the steps needed to get to your goal

Jon

 

[Ber]

Thanks for your help Jim..
Regards,
Ber
==========================================