VBA to create Dynamic Userforms

[matthew230]

Hi,

I'm interested in seeing if there is a technique to achieve the following:

I am building a reporting tool that queries a seperate database, what I need to do is somehow get a form with associated checkboxes to apply a filter, where the number of options are unknown and recreated each time.

For example, say a certain field had 3 seperate statuses, A, B or C and the string was returning results that matched those, I want a form with an associated Checkbox of all possible statuses. I then can construct the query.

The problem is that for one section I may have A, B, or C and another may include D and E aswell, therefore I need the form to be dynamic as the number of options is lkely to vary often. I can list the options to a worksheet to draw this data easily enough, I'm do not know how to create a dynamic form.

So any ideas or solutions greatly appreciated.

 

[Fazza]

Hi Matthew.

I started VBA with Walkenbach's "Excel 2000 Power Programming With VBA" and chapter 27 "Manipulating Visual Basic Components" has code for this sort of thing. I didn't find examples on line when I made a quick search just now.

However, I recall that earlier in the book is another idea. Something like create the user form with the full complement of buttons you require and then expose only the correct number for each situation. Either by adjusting the height of the user form or, and I might be imagining this, changing the visibility of some controls.

HTH. Regards, Fazza

 

[mikerickson]

I have a situation where I create an unknown number of checkboxes for a userform.
Actualy I create instances of a class clsInterestBox, which creates the checkboxes. So the event code for the checkboxes can reside in the Class module.

One option that I rejected, but might for your situation is to use a ListBox with .MulitSelect = fmMultSelectMulti and .ListStyle = fmListStyleOption.

This avoids the issue of event code, since the ListBox code is in place no matter how many entries are in the list.

 

[matthew230]

Thanks to both of you.

I'm going to have a look at list boxes, not used these yet and these may save me loads of time.

 

[chattympc]

I am trying to tackle a very similar problem and was wondering if you had been successful in solving this challenge.

 

[mikerickson]

chattympc,
If you start a thread describing your particular situation, you'll probably get better responses than reviving this old thread.

If your thread includes what you have tried and how that isn't working for you, a solution should be quickly found.