Dynamically generated forms in Excel? Possible?

Hello,
I'm wondering if its possible to dynamically generate a form with excel/vba.
I'm trying to pull the values from a range of cells and put them into a form with something like radio buttons so the user can pick from them. I just don't want to have a big form to accommodate a variable amount of field values, and then end up not using them all.
when I was thinking about the project I thought it would be easy, but then I remembered I was thinking like I was using active server pages, and then thought that doing this in vba may not work so well.
but maybe I'm wrong. Any thoughts would be appreciated.

Well, the strict answer to your question is "no". It is not possible (in general, using native VB) to generate forms.

However, I don't think that's really a problem, because it doesn't sound like it's really what you want to do. You just need to modify an existing form by

resizing it, by setting the .Height and .Width (http://msdn.microsoft.com/en-us/library/aa267517(VS.60).aspx) properties.


populating it with controls as needed. And the good news is that you can dynamically generate controls quite easily. The syntax is shown here (http://msdn.microsoft.com/en-us/library/aa277578(VS.60).aspx) for VB6, it's slightly different in VBA but easy to work out from the intellisense you get. The first parameter is the control type's "OLE Programmatic Identifier", which you can find in your Excel help by searching for that text.

You will want to store the references to the new controls somehow, as shown in the examples:
Set cmdObject = Form1.Controls.Add(...)
Now, the not-quite-so-good news is that if you need an indefinite number of these, you will have to use an array for this purpose, and controls stored thus cannot easily raise events. There is a workaround, but it's a little complicated. If you want things like radio buttons, you probably don't want events but just to read out the values at the end, so I'll not go into that for now.

Hope that helps. :)

Not wishing to appear contrary Cas :p

http://support.microsoft.com/kb/204330

There's also a nice example here which also shows how to add code (event handlers) to the userform code module, how to show the form, and how to delete the form when you're done. I believe it was originally written by John Walkenbach.

http://www.eng-tips.com/faqs.cfm?fid=110

D'uh. I did actually know that VBA supported Reflection in a limited way, but I've only ever seen it used to control the VBE before. Can I claim that by "native VB" I meant not using the VBA object model? Probably isn't going to hold up... :)

Sorry about that, jhahn, and thanks Timbo.

Edit: Hey, that also means one could provide full dynamic WithEvents control-array support without a callback-workaround by adding template code along with each new control, doesn't it?!

one could provide full dynamic WithEvents control-array support without a callback-workaround by adding template code along with each new control, doesn't it?!
You could do, but there's another way still:
http://proofficedev.com/blog/2007/02/26/control-arrays-in-vba/

:)

Yeah, that's what I mean by "callback workaround", although that term doesn't really apply to the version described in your link (I usually have the class array/collection in the controls' parent object). IMO Squirm's tutorial (http://www.xtremevbtalk.com/showthread.php?t=137599&highlight=withevents) on this technique is one of the highlights of Tutor's Corner.

I tried extending the method recently to allow for multiple event sinks as well as multiple event sources, without having to have a seperate collection in each control's wrapper. Ended up by using a "Hub" object that each event pipeline must be registered with. A bit clunky, but it allows for some additional nice things that are strictly forbidden by standard VB, such as having object A handling B's events and object B handling A's events. Or an instance of class A handling the events of another instance of class A.

Anyway, I'm curious now, what did you think I was referring to? Is there yet another way? :)

Hope this isn't going too far off topic...

that's what I mean by "callback workaround",
Oh ok, I think I missed the relevance of that. IMO it's a much "cleaner" approach than inserting code on the fly, but don't ask me to validate that opinion ;)

It sure is. And implementing it yourself for the first time has the nice side-effect of "de-mystifying" events, or at least it did for me.

The only advantage of providing a code-insertion framework rather than an event-bouncing framework is that it's probably easier to make it more flexible.

Thanks everyone for your help.
I didn't realize there was some functionality for this, and I'm not sure I would have known what to look for. I'll give the examples a try this week.
Thanks again.
-jeff

You could do, but there's another way still:
http://proofficedev.com/blog/2007/02/26/control-arrays-in-vba/

:)

Hmmm yes... but does the author of that article know what he's talking about??? :huh: :chuckle:

Oh ok, I think I missed the relevance of that. IMO it's a much "cleaner" approach than inserting code on the fly, but don't ask me to validate that opinion ;)

I agree with that too and this is the approach I personally employ when dynamically adding controls. I've never had the need to dynamically add a userform though. Trying to think of a good example when one would really want to?

Colin

I've never had the need to dynamically add a userform though. Trying to think of a good example when one would really want to?
Both the generic UserForm class and the generic control classes are non-creatable (Set = New syntax is not legal). That's why "dynamic creation" becomes an issue in the first place. However, the big difference between them, in this respect, is that you can derive your own form classes and that those are creatable (Set = New frmSomething works).
And since a dynamically added derived form is just as customizable as a dynamically added generic form, I'd almost be willing to bet that there can never be a good reason to do the latter, seeing as how it's so much more effort and so much less elegant. :)

Still, Reflection does have a certain appeal in and of itself, which could be reason enough to do it anyway... :p

Right, that's my thinking - having an "empty canvas" doesn't significantly increase the size of the file, so I couldn't come up with a situation (except for curiosity) where I would need to use:

Set mynewform = _
Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_MSForm)

Aha, file size, good call! It's a bit far-fetched, but it's the first remotely sound argument we have for using VBE... ;)

Workbook with three empty sheets -> 15 KB
Workbook with three empty sheets + one empty UserForm -> 19 KB
Workbook with three empty sheets + VBE reference -> 16.5 KB

I believe I thought of a scenario now in which creating forms on the fly might be of use: If you're VB6-automating a workbook that you haven't created yourself, it's quite conceivable that you'd prefer to show a VBA-UserForm rather than a VB-Form in a certain situation. So, your only choice would be to use VBA Extensibility.

Unless I'm overlooking something, and if that is possible from VB, that is. :)