Text Box Question

R

Rich Cooper

I am still looking for help on coding this idea. I have been told it can be
done but how or an example is what i need. If anyone can hele here is the
situation. . I have a colum of names, and i have alittle form with some
label boxs and a text box. The text box displays the name. I want to
be able to use the text box as a search function aside from just displaying
a name. So in other words i have a list of names and a text box. I want
you to be able type the name and it to auto complete as you type the name.
So say you have a list of 3 names: cooper, rich; cooper steven; smith, joel;
in the text box i have typed "smi" and it auto completes to smith, joel.
Can this be done and if so how?
 
H

Harald Staff

Hi

Sure. Assuming you already tried a combobox and didn't like it ? If not then
use a combobox.

This is the textbox from the controls toolbox, NOT the drawing toolbar or
from the Forms toolbar. They can't do this. You must write code for the
keydown event of the box, taking care of backspace, delete and those things.
Here's only the autocomplete part:

Option Explicit
Dim blnAuto As Boolean

Private Sub TextBox1_Change()
Dim L As Long
Dim S As String
Dim Found As Range
If blnAuto = True Then Exit Sub
S = TextBox1.Text
On Error Resume Next
Set Found = _
Sheet1.Range("A1:A1000").Find(What:=S & "*", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Found Is Nothing Then
Else
blnAuto = True
L = Len(TextBox1.Text)
TextBox1.Text = Found.Value
TextBox1.SelStart = L
TextBox1.SelLength = Len(TextBox1.Text)
blnAuto = False
End If
End Sub

HTH. Best wishes Harald
 
R

Rich Cooper

Coud i ask for your help to getting the backspace key to work. I would
appreciate that.
 
H

Harald Staff

(snip rest of thread -it's about an autocomplete Forms2 textbox)
Rich Cooper said:
Coud i ask for your help to getting the backspace key to work. I would
appreciate that.
Click to expand...

Ok. I never did this in VBA before, but I believe this is pretty close.
Complete code:

Option Explicit

Dim blnAuto As Boolean

Private Sub TextBox1_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 8 'Backspace
TextBox1.SelText = ""
If TextBox1.SelStart > 0 Then _
TextBox1.SelStart = TextBox1.SelStart - 1
TextBox1.SelLength = Len(TextBox1.Text) - TextBox1.SelStart
Case Else
End Select
End Sub

Private Sub TextBox1_Change()
Dim L As Long
Dim S As String
Dim Found As Range
If blnAuto = True Then Exit Sub
S = TextBox1.Text
On Error Resume Next
Set Found = _
Sheet1.Range("A1:A1000").Find(What:=S & "*", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Found Is Nothing Then
'write on
ElseIf TextBox1.SelStart = 0 Then
TextBox1.Text = ""
Else
blnAuto = True
L = Len(TextBox1.Text)
TextBox1.Text = Found.Value
TextBox1.SelStart = L
TextBox1.SelLength = Len(TextBox1.Text)
blnAuto = False
End If
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
Select Case KeyCode
Case 8 'Backspace
TextBox1.SelText = ""
If TextBox1.SelStart > 0 Then _
TextBox1.SelStart = TextBox1.SelStart - 1
TextBox1.SelLength = Len(TextBox1.Text) - TextBox1.SelStart
Case Else
End Select
End Sub

HTH. Best wishes Harald
 
Top