Auto-Complete with only text and not numbers ComboBox Excel VBA

Question

Suppose I have a ComboBox in Excel VBA with the following sorts of info:

1234 apples
2345 pears
2367 oranges

I want the user to be able to type 'a' or 'ap' or 'app' etc. So that auto-complete will suggest 1234 apples. As of right now the combo-box will only auto-complete if the user types the beginning part of the entry , i.e. in my case the numbers. Thanks

As far as I know that's not possible. Yet, it would be possible to have a text box adjacent to the ComboBox and as you type into that text box the ComboBox (or any item next to it) will automatically select / adjust the value or (at least) narrow down the available selections. If you are open to such a solution then I could draft an answer. — Ralph, Jun 01 '16 at 23:35
@Ralph I was thinking the same thing. You will use regular expressions? — IntegrateThis, Jun 02 '16 at 00:24
No, I try to avoid regular expressions whenever possible for two reasons: (1) RegEx is not native to VBA. As such you are dependent on an external library. As I had to develop for large enterprises in the past I try to include as few as possible libraries. The more libraries you depend on the higher the risk that your solution does not work on any computer in any of the 200+ countries. (2) RegEx makes you lazy. With them it is very easy to dissect text. It is harder if you do it yourself / manually. That's good training for your brain. :) — Ralph, Jun 02 '16 at 10:58
@Ralph Check out my solution which is rather elementary but so far does the job. I need to refine it a bit though so it is less awkward as described in my post. — IntegrateThis, Jun 03 '16 at 14:38

score 2 · Answer 1 · answered Jun 03 '16 at 17:21

Since you are using a form and form controls I'd like to throw in another solution (which I'd personally prefer). In this case I am not using a ComboBox but rather a ListBox:

This is the code to populate the ListBox on the form and to show the form:

Sub Button3_Click()

Dim i As Long
Dim lngLastRow As Long

Load frmSearchForChoices
With ThisWorkbook.Worksheets(1)
    lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lngLastRow
        frmSearchForChoices.lstAvailableOptions.AddItem
        frmSearchForChoices.lstAvailableOptions.List(frmSearchForChoices.lstAvailableOptions.ListCount - 1, 0) = .Cells(i, 1).Value2
        frmSearchForChoices.lstAvailableOptions.List(frmSearchForChoices.lstAvailableOptions.ListCount - 1, 1) = .Cells(i, 2).Value2
        frmSearchForChoices.lstAvailableOptions.List(frmSearchForChoices.lstAvailableOptions.ListCount - 1, 2) = .Cells(i, 3).Value2
    Next i
    frmSearchForChoices.Show
End With

End Sub

The following code resides on the form itself:

Option Explicit
Option Compare Text

Private Sub btnCancel_Click()

frmSearchForChoices.Hide
Unload frmSearchForChoices

End Sub

Private Sub btnOK_Click()

Dim lngMatch As Long

If frmSearchForChoices.lstAvailableOptions.ListCount > 0 Then
    If frmSearchForChoices.lstAvailableOptions.ListIndex >= 0 Then
        For lngMatch = 0 To frmSearchForChoices.lstAvailableOptions.ListCount - 1
            If frmSearchForChoices.lstAvailableOptions.Selected(lngMatch) = True Then
                MsgBox "You selected" & Chr(10) & _
                    frmSearchForChoices.lstAvailableOptions.List(lngMatch, 1) & " (" & _
                    frmSearchForChoices.lstAvailableOptions.List(lngMatch, 0) & ")" & _
                    IIf(Len(frmSearchForChoices.lstAvailableOptions.List(lngMatch, 2)) > 0, _
                        " from " & frmSearchForChoices.lstAvailableOptions.List(lngMatch, 2), "")
                frmSearchForChoices.Hide
                Unload frmSearchForChoices
            End If
        Next lngMatch
    End If
End If

End Sub

Private Sub txtSearchTerm_Change()

Dim i As Long
Dim lngMatch As Long
Dim varArray As Variant

If Len(Trim(frmSearchForChoices.txtSearchTerm.Value)) = 0 Then Exit Sub

For lngMatch = 0 To frmSearchForChoices.lstAvailableOptions.ListCount - 1
    frmSearchForChoices.lstAvailableOptions.Selected(lngMatch) = False
    frmSearchForChoices.lstAvailableOptions.List(lngMatch, 3) = 0
Next lngMatch


varArray = Split(Trim(frmSearchForChoices.txtSearchTerm.Value), " ")
For i = LBound(varArray) To UBound(varArray)
    For lngMatch = 0 To frmSearchForChoices.lstAvailableOptions.ListCount - 1
        If InStr(1, frmSearchForChoices.lstAvailableOptions.List(lngMatch, 1), varArray(i)) Or _
            InStr(1, frmSearchForChoices.lstAvailableOptions.List(lngMatch, 2), varArray(i)) Then
                frmSearchForChoices.lstAvailableOptions.List(lngMatch, 3) = Val(frmSearchForChoices.lstAvailableOptions.List(lngMatch, 3)) + 1
        End If
    Next lngMatch
Next i

For lngMatch = 0 To frmSearchForChoices.lstAvailableOptions.ListCount - 1
    If frmSearchForChoices.chkMatchBoth.Value Then
        If Val(frmSearchForChoices.lstAvailableOptions.List(lngMatch, 3)) >= UBound(varArray) - LBound(varArray) + 1 Then
            frmSearchForChoices.lstAvailableOptions.Selected(lngMatch) = True
        End If
    Else
        If Val(frmSearchForChoices.lstAvailableOptions.List(lngMatch, 3)) >= 1 Then
            frmSearchForChoices.lstAvailableOptions.Selected(lngMatch) = True
        End If
    End If
Next lngMatch

End Sub

I sure hope that most variables and controls can be identified within the code due to the followed naming convention (starting with frm for forms, lbl for labels on forms, ´lst` for ListBox on form, etc). Yet, do not hesitate to let me know if you have any questions regarding this solution.

IntegrateThis · Accepted Answer · 2016-06-03T16:26:23.170

So first to give some context to this answer. SectorDropDown1_1 (which is part of a form) is a drop down list that is populated with the concatenated strings of a number value and a string value coming from a column J. In this context the _change() method responds to the user typing in values. I have 2 columns "R", and "S", in a spreadsheet "SectorSearch" that have separated the number and the text. So now if the user enters either the number or the text, then the appropriate value in the Drop Down list is selected. But here is the problem, my code sort of awkwardly jumps to the exact value and so I want to "smooth things out" so to speak so that after the user types more than 2 characters that match the appropriate value that this value is now selected and the drop down list shows the nearby values.

Option Explicit

Private Sub SectorDropDown1_1_Change()

Dim i As Long
Dim StringRange1 As String
Dim StringRange2 As String
Dim Stringrange3 As String
Dim LengthOfValue As Integer
Dim TotalSectorCodes As Integer

If SectorDropDown1_1.Value <> "" And Len(SectorDropDown1_1.Value) > 2 Then
    TotalSectorCodes = Worksheets("SectorSearch").Range("J:J").Cells.SpecialCells(xlCellTypeFormulas).Count
    LengthOfValue = Len(SectorDropDown1_1.Value)
    For i = 2 To TotalSectorCodes
        StringRange1 = "R" & CStr(i)
        StringRange2 = "S" & CStr(i)
        Stringrange3 = "J" & CStr(i)
        Select Case SectorDropDown1_1.Value
            Case Left(Worksheets("SectorSearch").Range(StringRange1).Value, LengthOfValue)
                SectorDropDown1_1.Value = Worksheets("SectorSearch").Range(Stringrange3).Value
                Exit For
            Case Left(Worksheets("SectorSearch").Range(StringRange2).Value, LengthOfValue)
                SectorDropDown1_1.Value = Worksheets("SectorSearch").Range(Stringrange3).Value
                Exit For
            Case Else
                '...
        End Select
    Next i
End If

End Sub

I'm sorry. But I don't understand your solution. Also, what is `SectorDropDown1_1` and where is the value coming from? Maybe it becomes clearer if you were to include a screenshot or (possibly even) a GIF into your solution to show what your code does. Here is a sample GIF to show you what I mean: http://stackoverflow.com/questions/37239107/sort-range-without-sorting-it-in-a-spreadsheet/37239386#37239386 — Ralph, Jun 03 '16 at 15:17
@Ralph post edited to explain, will update more as I have more time today. — IntegrateThis, Jun 03 '16 at 15:21

Auto-Complete with only text and not numbers ComboBox Excel VBA

2 Answers2

Linked