Create UserForm programmatically

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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
===============================================
"Jon" <[email protected]> schreef in bericht
news:W9DUe.234651$HI.88715@edtnps84...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

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
news:[email protected]
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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> ===============================================
> "Jon" <[email protected]> schreef in bericht
> news:W9DUe.234651$HI.88715@edtnps84...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>>
>
>

Thanks for your help Jim..
Regards,
Ber
==========================================
"Jim Cone" <[email protected]> schreef in bericht
news:[email protected]...
> 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
> news:[email protected]
> 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