Announcement

Collapse
No announcement yet.

Auto Filtering ComboBox

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    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.
    Attached Files

  • #2
    Re: Auto Filtering ComboBox

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

    Comment


    • #3
      Re: Auto Filtering ComboBox

      I'm not sure which is more useful, the auto-filter or the Public Function in a user form whose only purpose is to get user input.

      Comment


      • #4
        Re: Auto Filtering ComboBox

        Just Brilliant!
        If the solution helped please donate to RSPCA

        Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

        Comment


        • #5
          Re: Auto Filtering ComboBox

          Thanks Mike...

          Check out our new reputation system. Click on the "star" under the post!
          _______________________________________________

          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

          _______________________________________________

          Comment


          • #6
            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.

            ChooseFromList ComboOrList.xlsm

            Comment


            • #7
              Re: Auto Filtering ComboBox

              Originally posted by mikerickson View Post
              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.
              Wow... you saved me. Thanks a lot. Highly appreciate it.

              Comment


              • #8
                Re: Auto Filtering ComboBox

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

                Comment


                • #9


                  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.

                  Code:
                  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
                  Attached Files
                  Last edited by excel1981; 1 week ago.

                  Comment

                  Working...
                  X