VBA: making this Auto-Filled Textbox.

[Peter h]

I'm doing this just to see if I can come up with how to do it. So, I've got a Userform with a single TextBox in it. I want it to auto fill and auto update as I type into it. I feel like I'm close, but I'm using the ".Find" function, and it's acting in a way I can't figure out. Here's the code:

Private Sub TextBox1_Change()
Dim ws As Worksheet
Dim lRow As Long
Dim x As String
Dim y As String
Dim c As Range
Set ws = ThisWorkbook.Sheets(1)
lRow = ws.Range("A" & Rows.Count).End(xlUp).Row
x = Me.TextBox1.Text
Set c = ws.Range("A1:A" & lRow).Find(x, , , xlPart, xlByRows)
If c Is Nothing Then
    Me.TextBox1 = x
    Exit Sub
End If
y = c.Text
Me.TextBox1 = y
    With Me.TextBox1
        .SetFocus
        .SelStart = Len(x)
        .SelLength = Len(y)
    End With
End Sub

So in my worksheet, I have a list of names sorted alphabetically:
Adam
Conrad
Eddie
Jennifer
Jerry
John
Kirby
Pete
Richard

It seems to work if I start typing any of the "J" names, Conrad, Kirby, or Pete. But for some reason when I try to type Adam or Richard, the textbox automatically fills with "Conrad". I can make sense of why that would happen when trying to type "Richard", because "Conrad" is the first cell it will find with an "R" in it. But I can't figure out why when I type "A" for Adam, it fills the textbox with "Conrad".

Am I going about this wrong? Thanks for any help.

 

[Kenneth Hobson]

Re: VBA: Need some help making this Auto-Filled Textbox.

I like to use Combobox and Filter() to drilldown a list.

I don't know how to fix the first part right off but for the 2nd:

Set c = ws.Range("A1:A" & lRow).Find(x, ws.Cells(lRow, "A"), , xlPart, xlByRows)

If you set MatchCase to True, you can take care of the first character Proper case issue too.

Set c = ws.Range("A1:A" & lRow).Find(x, ws.Cells(lRow, "A"), , xlPart, xlByRows, , True)

 

[Peter h]

Re: VBA: Need some help making this Auto-Filled Textbox.

I like to use Combobox and Filter() to drilldown a list.

I don't know how to fix the first part right off but for the 2nd:

Set c = ws.Range("A1:A" & lRow).Find(x, ws.Cells(lRow, "A"), , xlPart, xlByRows)

If you set MatchCase to True, you can take care of the first character Proper case issue too.

Set c = ws.Range("A1:A" & lRow).Find(x, ws.Cells(lRow, "A"), , xlPart, xlByRows, , True)

Thanks for the reply. I'll mess around with it. I agree that Comboboxes are ideal for this, I was just seeing if I could come up with a way to have a textbox mimic how like a cell autofills.