Auto completion in textbox

[kelly mort]

Hello all
I have a challenge here:
I want my textbox to behave in such a way that when I start to enter data in it, it should look at a range in a worksheet and then suggest words or names as I type the letters.

So say I have a name "Kelly" in that range, when I start to type "K" , it should suggest "Kelly" for me to make my work cooler.


I am stucked. I need bigger brains to pull me out. Thanks
Kelly

 

[My Aswer Is This]

You need to use a Activex Combobox for this.

A Textbox will not work for this:

 

[kelly mort]

You need to use a Activex Combobox for this.

A Textbox will not work for this:

Active Combobox? How? Can you give an example?

Thanks
Kelly

 

[My Aswer Is This]

Tell me where your list of values are you want to choose from.
Tell me where you want the value entered when you choose the value.

So we are going to load all the values you want to choose from into a Activex Combobox

Then if you have: "George is a nice guy" loaded into the Combobox

When you enter Geo into the combobox the value you see will jump to "George is a nice Guy"

Then I assume you want this value entered some other place.
Then when you click outside the combobox the value you choose will be entered where you want.

So for example you need to tell me:

The values I want loaded in the combobox to choose from are in the range("A1:A75") or something like this

 

[kelly mort]

Oh okay it seems I am not clear a bit here:

I want the textbox behave as we have in Excel cells, that when you have entered say "This is very important." and you later start to enter in a new cell "Th " then it suggests the above sentence.


Is there a way to achieve that?

Thanks

 

[My Aswer Is This]

I told you before as far as I know there is no way to do that.
I gave you another option but you must not like that option.

 

[kelly mort]

I told you before as far as I know there is no way to do that.
I gave you another option but you must not like that option.

Okay thanks for the effort
Kelly

 

[NdNoviceHlp]

U can trial something like this with data in "A" of sheet1 and an activeX textbox on the ws (ie. sheet code)....

Option Explicit
Dim Flag As Boolean

Private Sub TextBox1_Change()
If TextBox1.Text = vbNullString Then
Flag = False
End If
If Not Flag Then
Call test
End If
End Sub

Sub test()
Dim Lastrow As Long, Cnt As Long
If TextBox1.Text <> vbNullString Then
Lastrow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For Cnt = 1 To Lastrow
If Left(Sheets("Sheet1").Range("A" & Cnt).Value, Len(TextBox1.Text)) = TextBox1.Text Then
If MsgBox(prompt:="Replace with: " & Sheets("Sheet1").Range("A" & Cnt).Value, _
                                   Buttons:=vbYesNo, Title:="REPLACE?") = vbYes Then
Flag = True
TextBox1.Text = Sheets("Sheet1").Range("A" & Cnt).Value
Exit Sub
End If
End If
Next Cnt
End If
Flag = False
End Sub

U didn't say what U want to do with the found word/sentence. I'm guessing put the textbox contents somewhere and reset the textbox. So an activeX command button on the ws
would put the contents in "B1"of sheet1 and reset the textbox. HTH. Dave

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("B" & 1).Value = TextBox1.Text
TextBox1.Text = vbNullString
End Sub

 

[kelly mort]

U can trial something like this with data in "A" of sheet1 and an activeX textbox on the ws (ie. sheet code)....

Option Explicit
Dim Flag As Boolean

Private Sub TextBox1_Change()
If TextBox1.Text = vbNullString Then
Flag = False
End If
If Not Flag Then
Call test
End If
End Sub

Sub test()
Dim Lastrow As Long, Cnt As Long
If TextBox1.Text <> vbNullString Then
Lastrow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For Cnt = 1 To Lastrow
If Left(Sheets("Sheet1").Range("A" & Cnt).Value, Len(TextBox1.Text)) = TextBox1.Text Then
If MsgBox(prompt:="Replace with: " & Sheets("Sheet1").Range("A" & Cnt).Value, _
                                   Buttons:=vbYesNo, Title:="REPLACE?") = vbYes Then
Flag = True
TextBox1.Text = Sheets("Sheet1").Range("A" & Cnt).Value
Exit Sub
End If
End If
Next Cnt
End If
Flag = False
End Sub

U didn't say what U want to do with the found word/sentence. I'm guessing put the textbox contents somewhere and reset the textbox. So an activeX command button on the ws
would put the contents in "B1"of sheet1 and reset the textbox. HTH. Dave

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("B" & 1).Value = TextBox1.Text
TextBox1.Text = vbNullString
End Sub

Oh okay thanks very much for this. I love it.

But can we turn off the case sensitive?

 

[NdNoviceHlp]

Thanks for posting your outcome. Dave

Sub test()
Dim Lastrow As Long, Cnt As Long
If TextBox1.Text <> vbNullString Then
Lastrow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For Cnt = 1 To Lastrow
If UCase(Left(Sheets("Sheet1").Range("A" & Cnt).Value, Len(TextBox1.Text))) = TextBox1.Text Or _
LCase(Left(Sheets("Sheet1").Range("A" & Cnt).Value, Len(TextBox1.Text))) = TextBox1.Text Then
If MsgBox(prompt:="Replace with: " & Sheets("Sheet1").Range("A" & Cnt).Value, _
                                   Buttons:=vbYesNo, Title:="REPLACE?") = vbYes Then
Flag = True
TextBox1.Text = Sheets("Sheet1").Range("A" & Cnt).Value
Exit Sub
End If
End If
Next Cnt
End If
Flag = False
End Sub