Auto Filtering ComboBox

Re: Auto Filtering ComboBox

very nice Mike, and something that gets asked for often.

Re: Auto Filtering ComboBox

Just Brilliant!

Re: Auto Filtering ComboBox

Thanks Mike...

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.

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

Re: Auto Filtering ComboBox

Quote from mikerickson;765941

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

Dim myVal as string


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.

Display More

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

Re: Auto Filtering ComboBox

Just to say thank you ! Working good, customizing that is pretty easy

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.

Private IsArrow As Boolean
Private Sub ComboBox1_Change()
Dim i As Long
    With Me.ComboBox1
        If Not IsArrow Then .List = Range("AccountsList").Value
        If .ListIndex = -1 And Len(.Text) Then
            For i = .ListCount - 1 To 0 Step -1
                If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
            Next i
            .DropDown
        End If
    End With
End Sub


Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
If KeyCode = vbKeyReturn Then Me.ComboBox1.List = Range("AccountsList").Value
End Sub

thanks! A lot this is what excatly im looking for. but how to remove duplicates from drop down list? can u please help add the code to remove duplicates?

Very Handy Auto Filtering ComboBox ... :smile:

Thanks a lot ...!!!

This is great! Is there a way to implement this code for a ComboBox that's embedded in the worksheet itself and not a UserForm? Thanks!

Very use full code for auto complete any where in the text. Great work

Great Solution for Filtering Combobox