DYNAMICALLY CREATE FORM 2

[lazytrucker]

I have done some research on this subject and have had no luck.

Basically depending on the previous results in my script I want to create a form for user input, simply if txtCount = 4 I want to create a form with 4 i/p fields if txtCount = 5, then 5 i/p fields are created etc.

Does any one know if it's possible and where I can find out how to do it??

Cheers LazyTrucker

 

[Killian01]

I don't believe it's possible to create controls at rum time in VBA (this would by done using a control array in VB) but can accept working within a range of possibilities (say 2 to 10) then you can create all the controls at design time and run a script that sets their 'visible' property and the adjust the form 'height' to make it all fit
Not the last word in elegance but there we are...
enjoy
Killian

 

[Killian01]

and i don't think you can create them at RUN time either

I'll learn to type one day too...

 

[Phaed]

You can not create UserForm controls at run-time, but you CAN create ActiveX controls in a document at run-time. So if you need to use a UserForm...no. If you want to use an in-line form, yes you can.

 

[TonyJollans]

Hi lazytrucker,

It is certainly possible to dynamically create userforms and controls at run time. If you search this forum for, say, dynamic forms, you should find some info but do come back if you have any problems.

To get you started ..

[blue]Set frm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
Set txt = frm.Designer.Controls.Add("Forms.Textbox.1")
Set btn = frm.Designer.Controls.Add("Forms.CommandButton.1")
btn.Top = 50
VBA.UserForms.Add(frm.Name).Show[/blue]

.. will create and display a basic form for you. You'll need to play with the various size and position properties to get it looking as you want, and you'll need to add code to it to work with the variables, for example ..

[blue]With frm.CodeModule
     Line = .CountOfLines
     .InsertLines Line + 1, "Sub " & btn.Name & "_Click()"
     .InsertLines Line + 2, "strText1 = Me." & txt.Name
     .InsertLines Line + 3, "End Sub"
End With[/blue]

If strText1 is a public variable in your module you will be able to reference it afterwards and do as you wish.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[lazytrucker]

Hi Tony I see what your getting at here, and thanks for not telling me it simply wasnt possible, but when I try to run the sub I get an error:

Run-Time Error'1004:

Programmatic access to visual basic project is not trusted.

This occurs on the first line of code:

Set frm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

I am running this as a module in an xla file to work with excel.

Also "vbext_ct_MSForm" was required to be set as a var is this all correct??

Cheers LazyTrucker.

 

[TonyJollans]

Hi lazytrucker,

Firstly, you need a reference to "MicrosoftVisual Basic for Application extensibility x.x" to use vbext_ct_MSForm, or you can use the value 3 instead.

I haven't time right now, and I only have Excel2000, but I think your problem is to do with security settings in XP or 2003. Off the top of my head i don't know how to change them (assuming it can be done - presumably not programmatically).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[taupirho]

I certainly agree with Tony. I know for a fact its possible to programmatically create userform controls at run-time as I've done it. Check out my article below as an example of how to create a flexgrid at run time and why I had to do it this way.

http://the-big-o.port5.com/article51.shtml

 

[BrainB]

Yes it is possible. I see you have some starters already. There is loads of code around.

Personally I would set up a userform with more textboxes than I need and hide the unwanted ones. Perhaps resizing the form with code too. Dead easy. Not only would this make (coding/layout)life easier but it is better practice to do as much work as possible before runtime. Less chance of system crashing.


Regards
BrianB
Use CupOfCoffee to speed up all windows applications.
It is easy until you know how.
================================