Auto Filtering ComboBox

  • This is tool for the user to choose from a list. Its a combo box on a user form, but with two twists.
    1) As the user types in the box, the list is filtered.
    2) It is cast as a public function of the user form. The coding to call it.


    Code
    1. Dim myVal as string
    2. MyVal = Userform1.ChooseFromList(Array("a", "b", "c", "d"), "Pick one")


    The xlFilterStyle argument of the ChooseFromList function controls what kind of filtering xlBeginsWith, xlContains, xlEndsWith, xlDoesNotContain or xlNone.
    And it allows for a default answer.
    It also accepts ranges as the source of the list.


    The four buttons show some ways of how it might be used.
    If you "Choose a group", try to choose a group that contains Brendan.


    I hope you find a use for this.

  • Re: Auto Filtering ComboBox


    Thanks Mike...

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Auto Filtering ComboBox


    Mike,


    Great code. I've already referenced it in an answer in the Help forum.


    I hesitate to say anything, but I ran into an issue when I tried searching for the literal left bracket. I don't remember why I tried to put that in the ComboBox. I must have just been hitting keys. I wasn't trying to break it, I promise! Since [ is a special character, the code gave an error because I didn't have a corresponding right bracket to complete the character set (or I suppose error handler code).


    However, then I got thinking that while using wildcards is kinda cool, the user might want it off in case part lists, for example, use brackets or asterisks and the literals of them are to be found.


    I added a UseWildcards boolean to the Public Function call that controls a global WildcardsOn to know how to deal with these special characters typed in by the user: [, #, ?, *. If the UseWildcards is false and the user types in one of these characters, I replace the filter text with that character placed between brackets. E.g., if someone types dav? and the ? is meant to be literal (wildcards off), the code changes it to dav[?]. Any text in the list with "dav?" (davQuestionMark and not davAnySingleCharacter) will be filtered according to the filter. So that I don't write over the ComboBox text with the additional brackets, I use a string variable.


    If wildcards are on, then the only thing the code has to deal with is if the user has an unpaired left bracket. The other three wildcards can just be left alone. If the left bracket isn't paired, an InputBox has the user fix it. The user can, however, have more right brackets than left brackets, which will look for the unpaired right brackets as literals.


    While I was at it, I redim FilteredItems in the Change event so that the list length is also dynamic.


    Then, for my own benefit, I converted the ComboBox to a ListBox/TextBox combination. I like it better. I also provide that here in case that is useful. There is another userForm and Module calling it for examples in this file. The code is mostly the same with a few additions to handle the difference. The FilterStyle and WildcardsOn are also selectable in the userform using Radio controls.


    ozgrid.com/forum/core/index.php?attachment/68438/

  • Re: Auto Filtering ComboBox



    Wow... you saved me. Thanks a lot. Highly appreciate it.

  • Hi mike nice
    One thing is missing .use of up/down arrows on the filtered results


    In the attached workbook this deficiency is made up.
    Courtesy for this code goes to Alfa Frog in another forum.