Results 1 to 6 of 6

VBA to create Dynamic Userforms

This is a discussion on VBA to create Dynamic Userforms within the Excel Questions forums, part of the Question Forums category; Hi, I'm interested in seeing if there is a technique to achieve the following: I am building a reporting tool ...

  1. #1
    Board Regular
    Join Date
    Jan 2006
    Posts
    152

    Default VBA to create Dynamic Userforms

    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.

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,918

    Default Re: VBA to create Dynamic Userforms

    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

  3. #3
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,938

    Default Re: VBA to create Dynamic Userforms

    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.

  4. #4
    Board Regular
    Join Date
    Jan 2006
    Posts
    152

    Default Re: VBA to create Dynamic Userforms

    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.

  5. #5
    New Member
    Join Date
    Nov 2009
    Posts
    1

    Default Re: VBA to create Dynamic Userforms

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

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,938

    Default Re: VBA to create Dynamic Userforms

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com