autocomplete in a textbox using vba

[Corman]

Hi all. I hope someone can help me with this one.

I have an excel file with a large amount of data. It is used to keep track of our firms past and current projects (on each row). The columns include data such as the name of the client and the city in which the project is located.

The excel sheet is set up with filters, so that it is easy to check which jobs we've done for client 'X', for instance, or the jobs initiated since a certain date.

In order for the filters to work properly, we need to make sure that, as an example, teh client name has to always be inputed in the same manner. Using excel's autocomplete option works well for this.

New projects are added to the top of the list. So, a new row has to be added each time a new project is inputted.

In order to automate things, I've made a userform with text/comboboxes in which the data for the new project is inputted and then placed onto the sheet once the userform is closed.

My question is this:

Is there a way to have the text/comboboxes on the userform use the autocomplete feature? As an example, if I'm entering the client name for a new project, is there a way to have the textbox autocomplete the client name by checking in the client column on the sheet? And, if there is more than one client starting with the same letter, is it possible to make a listbox appear showing all the clients beginning with that letter so the correct one can be picked?

Since the list of clients will always be changing, I don't want to code a certain set list, or have to continually update a list. (besides, the amount of coding would be huge considering the number of clients I would have to give it's own .AddItem)

Any help or guidance is appreciated!

Regards,

Corey D.

 

[longjohn]

I could not make this thing work. I'm very new at VB, so please be patient...

Anyways, I did not use a userform, but placed a text box directly in the worksheet. I then had these issues:
1. i got 'type mismatch' on the call to MyAutoComplete
2. Enter and Exit events are not called (I commented out the call to MyAutoComplete so events are turned on)

Thank you for your help
John

OK, I've sorted out the delete thing. However, I don't have enough information to determine where you want the line inserted. If the line is always inserted in the same place then you can hard code which cell you want the data to go into. To use this example, set up a workbook as I described in my first post, but before running the userform, insert a row manually above row 1. Here is the code:



Option Explicit
Dim oRange As Range
Dim iCharCount As Integer
Dim sAuto As String
Dim sTemp As String


Private Sub TextBox1_Enter()
Set oRange = Worksheets("Sheet1").Range("a35536").End(xlUp).Offset(1, 0)
End Sub

Private Sub MyAutoComplete(ByRef oTextbox As Control)
oRange.Value = oTextbox.Text
sAuto = oRange.AutoComplete(oTextbox.Text)

If Len(sAuto) > 0 Then
With oTextbox
sTemp = .Text
.Text = sAuto
.SelStart = Len(sTemp)
.SelLength = Len(sAuto)
End With
End If
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'++++++++++++++++++++++++++++++++++++
'Change this next line if you are going to hard code which cell gets the data
Sheets("Sheet1").Range("a1").Value = Me.TextBox1.Text
'++++++++++++++++++++++++++++++++++++
oRange.ClearContents
End Sub

Private Sub TextBox1_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode >= 48 And KeyCode <= 90 Then 'Alphanumeric only
Application.EnableEvents = False
MyAutoComplete Me.TextBox1
Application.EnableEvents = True
End If
End Sub</pre>

Now this part you'll need to do for yourself. The code for inserting a new line is simply:



Sheets("Sheet1").Rows("1:1").Insert Shift:=xlDown</pre>

Where "Rows("1:1")" indicates that the inserted row will become Row1. If you want to insert a row that will become row2 then simply change this to Rows("2:2").

I hope you get the idea. the cod eis set up to update the target cell, in this case cell A1 on Sheet1 only after the user has finished typing in the text box.

HTH


EDIT:: Oops, I nearly forgot. I've limited your data entry to alphanumeric characters so that you can delete, backspace and use the arrow keys as normal.
_________________
Mark O'Brien

Columbus Ohio Celtic Supporters Club

[SIZE=-1][ This Message was edited by: Mark O'Brien on 2002-12-09 22:33 ][/SIZE]

 

[Seroo]

I registered here just to say thanks and report that the code still works in office 2013.

This was really helpfull.

 

[kinetik]

I registered here just to say thanks and report that the code still works in office 2013.

This was really helpfull.

Also wanted to say thanks for this code and that it still works in 2016 --

Also a note

Private Sub YOURTEXTBOX_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode <> 8 Then ' This makes the delete key work great without auto updating before you go to type a new value!


    Application.EnableEvents = False


    AutoComplete Me.YOURTEXTBOX


    Application.EnableEvents = True
End If
End Sub

Just thought I'd add an update to 14 year old code!

 

[huzz456]

Sorry for digging up an old thread but this is almost exactly what im after apart from one thing. My list has some items with a large part of the name the same as others.

Example:

Chicken (Quarters)
Chicken (Whole)

What this means is that I have to type almost the entire text before it auto completes (at the letter "Q" in this example).

Is there a way to have a suggestion box appear underneath that auto updates as you type so you can select the one you want quickly? Possibly using a combobox rather than text box perhaps?