Use AutoFilter results from NamedRange in RowSource ComboBox

[Pastafarian]

Hi,

Been trying this for a long, long time now and I've yet to see a solution.

I have a sheet with names of customers in column A.
I also have a userform with a combobox. Its RowSource currently is the named range of all customers in A.
I now want users to search for customers containing some of the letters typed in the combobox.

So far i've tried:
-Autofilter + RowSource (not working)
-Autofilter + additem (working, but has duplicates and does not autofill the entire name) Code:

With Sheets("Customers")
    .AutoFilterMode = False
    .Range("A1:A1000").AutoFilter
    .Range("A1:A1000").AutoFilter Field:=1, Criteria1:="=*" & ComboBox1.value & "*"
End With

For Each rCell In Sheets("Customers").Range("A1:A10").SpecialCells(xlVisible)
    With Me.customers
        .AddItem rCell.Value
    End With
Next rCell

What is the best (most userfriendly and preferably easiest) way to do this?


Thanks in advance!

 

[Jerry Sullivan]

Hi Pastafarian,

I believe .AddItem (or a combination of .AddItem and .List) would be a good approach.

One problem with your current .AddItem attempt is that after applying the AutoFilter, you are only adding items that are in the first 10 rows of Column A.
Even if you only have 10 or less visible items, you'll need to reference their actual rows instead of "displayed" rows.
As an example, if the second match is in Cell A42 it won't be found by referencing Range("A1:A10").SpecialCells(xlVisible)
That being said, IMO using Autofilter makes this overly complicated and I'd recommend you make your list using Range.Find.

Please clarify a few things....
1. What do you mean when you said "...does not autofill the entire name"
2. How is this code triggered...the user begins entering text into the ComboBox then at what point should the code start finding matches and populating the ComboBox?
3. This thread's title refers to a named range, but there isn't one mentioned in the code. Should a name be used in place of A1:A1000? Does the named range include a header row?

 

[Pastafarian]

Thanks for the response.
1. By "autofill" the entire name" , I mean that if you type for example: "Portugal" and there's only 1 customer called Portugal it won't automatically select that customer (and thus automatically fill the combobox).
2. Code is triggered by ComboBox_Change, so as soon as user start typing it should look up the correct match.
3. I used the named range in the first attempt (haven't posted code from that), no named range in code. named range are all the customers (say A2:A1000) and does not include header row.

 

[Jerry Sullivan]

In trying to get this to behave the way a user might expect, some interesting challenges arose.

1. Closing the ComboBox dropdown once a unique match is found.

2. Handling backspaces once the entire match text is displayed (if there is still a unique match after the last letter is removed, how to get the code to allow the backspace without continually replacing with the entire match text).

This isn't ideal, but I think it provides the functionality you described.
Copy all the code into the Userform code module.
If you are already using some of the event procedures (such as UserForm_Initialize), those procedures will need to be merged together.

Option Explicit

    
Public m_DisableEvents As Boolean
Public m_Backspace As Boolean

Private Sub ComboBox1_Change()
    Dim sFindText As String, sFirstAddr As String, sKey As String
    Dim lIdx As Long, i As Long
    Dim cFound As Range
    Dim vArray() As Variant
    
    '--supress effects of Combobox triggering itself or on backspace
    If m_DisableEvents Then Exit Sub
    
    '--read and validate search entry
    sFindText = Me.ComboBox1.Value
    If Trim(sFindText) = "" Then sFindText = "*"

    With Sheets("Customers").Range("Customers")
        On Error Resume Next
        Set cFound = .Find(What:=sFindText, After:=.Cells(.Rows.Count), _
            LookIn:=xlValues, LookAt:=xlPart, _
            SearchDirection:=xlNext, MatchCase:=False)
        On Error GoTo 0

        If cFound Is Nothing Then
            MsgBox "No Match was found for '" & sFindText & " '", vbExclamation
        Else
            sFirstAddr = cFound.Address
            ReDim vArray(1 To .Rows.Count)
            lIdx = 0
            Do Until cFound Is Nothing
             '--add unique matching items to array
                If IsError(Application.Match(cFound, vArray, 0)) Then
                    lIdx = lIdx + 1
                    vArray(lIdx) = cFound.Text
                End If
                Set cFound = .FindNext(After:=cFound)
                If cFound.Address = sFirstAddr Then Exit Do
            Loop
            ReDim Preserve vArray(1 To lIdx)
            '--place the array or single match in the ComboBox
            With Me.ComboBox1
                If lIdx > 1 Then
                    .DropDown
                    .List = Application.Transpose(vArray)
                Else
                    m_DisableEvents = True
                    If Not m_Backspace Then
                        .Value = vArray(1)
                        '--use another control to close DropDown
                        Me.CommandButton1.SetFocus
                        .SetFocus
                    End If
                End If
            End With
        End If
    End With
    m_DisableEvents = False
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    m_Backspace = IIf(KeyCode = 8, True, False)
End Sub

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .MatchEntry = fmMatchEntryNone
        .RowSource = ""
    End With
End Sub

The code assumes you have a Named Range "Customers" on Sheet "Customers" - edit to match your actual names.

The code further assumes you have a CommandButton1 control in your userform that is used to temporarily take focus from the ComboBox. You could change that to another control in your userform that can take focus. An alternative to this technique is to use SendKeys "{ESC}", but that method can be problematic.

 

[qudsia.hussain]

Hi Jerry!

Happy New Year

I'm trying to do something much simpler (I think..) with a macro but I'm not able to figure out why the criteria is not returning the right results. Here is my code:

Range(ActiveCell.CurrentRegion.Address).AutoFilter Field:=ActiveCell.Column, Criteria1:=Array( _
"HANNA*", "MARK*", "LUTHER*")

So, basically I need my filter to do a "begins with" filter on the mentioned names. But the macro seems to return filter results only based on the last name mentioned in the Array and ignores the rest. Any idea how I can fix this, or if I need to change my code completely? Please help!

P.S.: Apologies if I needed to post a completely new thread..I just figured this post sort of seemed in line with what I was attempting to achieve (or not! :-/).. Still kinda of new to the forum.


Thanks,
Q.

 

[Jerry Sullivan]

Hi Q, Happy New Year to you as well.

Are you using the results of this filter as the list for a ComboBox in a UserForm, or just wanting to AutoFilter a range for mutiple "begins with" criteria?
If it's that latter, then I'd suggest you use Advanced Filter.

P.S.: Apologies if I needed to post a completely new thread..I just figured this post sort of seemed in line with what I was attempting to achieve (or not! :-/).. Still kinda of new to the forum.

Some Excel Forums require users that didn't start the thread to start a new thread for related questions (and to add a link if it is helpful).
The MrExcel forum allows other users to ask related questions. In my opinion the decision whether or not to start a new thread should be based on how closely related the question is.

If you want help implementing AdvancedFilter for this, perhaps best to start a new thread- I'll be glad to help if someone else doesn't respond first.
If your question relates to a ComboBox on a UserForm, then we could continue that discussion here.