PDA

View Full Version : UserForm - Add and Use Controls at RunTime.



mitko007
11-09-2013, 01:22 AM
Hi,

i am struggling with an issue regarding a userform and i would appreciate your help. Basically i have the following problem. I have created a Userform (manually in the VBA Projectbrowser). I have written a VBA code, which fills this Userform with different Objects in runtime (Labels, Optionbuttons etc.). So far everything worked fine - the Userform is filled with data read from my excel sheets and correctly displayed. However i'm not able to read the inputs from the objects on it (for example Optionbutton - TRUE or FALSE). These objects do not appear anywhere (except on the userform) so that i can link them and use them in anoter Module for example. I guess they are only displayed and not really read into the memory or whatever (initialized !?). I am a beginner and cant find a way to get over this problem.

Thanks in advance

ashleyuk1984
11-09-2013, 05:23 AM
Please upload your file - or an example so that we can examine your problems and see what's wrong or missing.

mitko007
11-09-2013, 09:21 AM
Thanks for your response. I've uploaded an example. A short description of what i need can be found on sheet1

Regards

Rob342
11-10-2013, 01:35 PM
In my opinion its much better to set the form up with the option buttons already there, if not then put the code to create in the initialize routine.
see example attached
Rob

SamT
11-10-2013, 02:54 PM
Now we know how you are trying to solve the issue.

Can you tell us what you are really trying to do?

We might know a simpler, easier, or just better way to do it.

mitko007
11-11-2013, 10:00 AM
SAmT,
i am trying to create a small macro that will help me a lot. Basically i need a Userform, which should be filled with objects in realtime..lets say Optionbuttons as a start. So far i managed to do everything, as i created a empty userform manually and then in a Sub Module, created the code that places the objects on it. However, after this is done and the Userform has all necessary objects on it, i want to be able to use it and execute different operations depending on what i have selected. Lets say if i select one of my optionbuttons (which where created in runtime) i need to write a value in a specific cell on a sheet. I hope i explained myself well. I think the problem is that all those objects should be Public or something like this but i'm not sure.

SamT
11-11-2013, 02:55 PM
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H. (http://www.vbaexpress.com/forum/showthread.php?47641-INDIRECT%28%29-function-doesn-t-work-quot-Can-t-Find-Object-or-Library-quot&p=297932&viewfull=1#post297932)

I'm with Rob. I can only think of one circumstance where it is best that Controls be created at Run Time.

But, if you insist on doing it how you want to...


I think the problem is that all those objects should be Public or something like this but i'm not sure.

Nope. You need a way to access the control after it is created. You can Name the control and refer to it that way; you can add a Tag value. Or, you can track their index numbers starting with Form.Controls.Count + 1 as the starting count. If all the Optionbuttons are created at Run Time, you can shrink the Loop by filtering them by Type.

Mitco, I tell you, for the third time, and no more, that you are making a mistake.

You have elected to populate a UserForm with Controls in the most complicate and difficult way possible. At Run Time.

here's what my signature means for you;
What we know that you are trying to do is to create a Data Entry Form. (Very broadly.)
How you want to do it is by populating its Controls at Run Time.

We don't know What specifically you want the UserForm to do.

Teeroy
11-11-2013, 03:01 PM
Another method (which is much easier to use) is to load all the controls you'll need for all possibilities into the UserForm, then set the visible property to False for those not required for each situation.

Rob342
11-11-2013, 04:33 PM
Mitko
I personally would not go down that route, much better to create all controls 1st, you will lose track of what controls you have created & where
Take SamT's advice, you can put the controls in a frame then hide the frame as Teeroy has pointed out. It's a lot easier, keep it as simple as you can
Rob

mitko007
11-11-2013, 11:36 PM
Leeroy, Rob - i can't create them at the beginning because i don't know how much of them will need. I may need 10 Optionbuttons as well as 20 in some cases.

Sam - Sam there is an example file attached on my first post. You can see there WHAT i want to have.

mitko007
11-11-2013, 11:39 PM
Thanks Rob, but that's exactly my problem. I can't place the button there at the start, since there are some criteria i follow whether a button should be placed on the userform or not. Therefore i went for the runtime creation of the objects.

SamT
11-12-2013, 04:56 PM
I don't know if this would work for you, but the only time I ever had to add controls at runtime, I built a class object that contained a list box. I then intantiated that class and added it to the form with a different name, each time I needed a new listbox. My class had two Events, "Click", that both added a new class object ListBox below it and set a hidden Label to its' selected value; And "BeforeClose," that closed the Obect below it. I then used the Label's value where I needed it.

I am going to change the title of this thread to see if we can attract more help for you.

mitko007
11-12-2013, 11:59 PM
I got some help with my example. The guy that helped me said that the code work with him. However i always get a "NO" as a result.

Could be helpful perhaps.

Aflatoon
11-13-2013, 01:33 AM
Try this one. I have added some comments in the code to try and explain what is happening.

mitko007
11-13-2013, 03:04 AM
Alfatoon, thanks mate. It looks as this one works fine. I'll check it later, but at first glance it does the job. I guess i'll just have to add groups (with the counter) in order to separate all buttons, right ( i would like to have the option to choose more then one Yes) ?

Aflatoon
11-13-2013, 03:12 AM
Yes - you'll need to add whatever changes you require. ;)

mikerickson
11-14-2013, 07:26 AM
Rather than create option buttons at run time, one can use a (design time) listbox, with .ListStyle set to fmListStyleOption, to record the users choice.

If your need is to create checkboxes, a similar listbox, set to Multiselect is an option.