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.

 

[Corticus]

Hi!

I don't think you can make a textbox autoupdate without a sick amount of VBA. An easy workaround, I believe, would be to use a combobox instead. Combobox's have an 'autowordselect' property. Instead of have the combobox refer to the main record list, you could avoid duplicates of the same name in the combobox by linking it to a new sheet which uses an advanced filter to filter only unique values. You could have the combobox1_afterupdate event to trigger the advanced filter so a new name will be in the list the next time you need it...

If you need more detail, post back...

edit:
I would give more detail, but I think Mark's solution is better...

HTH,
Corticus
This message was edited by Corticus on 2002-12-10 10:04

 

[Corman]

Hi Corticus, thanks for the reply.

Yeah, I think I'll need more detail (the more the better!) I'm not that well versed in VB; I've just learned by looking at code and doing internet searches, etc.

Thanks,
Corey

 

[Mark O'Brien]

Here's a quick example. Of autocomplete. I've not completely debugged it or anything. I started with a nice new Excel workbook. In A1 I wrote "Mark". In "A2" I put "MrExcel". I added a userform (userform1) and placed a textbox on this userform (textbox1). I then created this code to "Autocomplete the userform textbox using column "A". I tested the textbox by typing in "M" then "a". I also tested using "M" then "r". As I say this is just an example that it is possible and reasonably painless. It can be tailored to suit your needs:

<pre>
Option Explicit
Dim oRange As Range
Dim iCharCount As Integer
Private Sub TextBox1_Change()
Dim sAuto As String
Dim sTemp As String

Application.ScreenUpdating = False
Application.EnableEvents = False
sTemp = Me.TextBox1.Text
oRange.Value = Me.TextBox1.Text
sAuto = oRange.AutoComplete(Me.TextBox1.Text)

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

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

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

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
oRange.ClearContents
End Sub</pre>

HTH

 

[Corman]

Thanks Mark! This is very close to what I'm looking for.

A couple of things though:

1) I want the data to be placed above the current data, in a new row. with your code, it uses the next empty row down (i.e. adds a row at the end). Is there a way to insert the new data onto the first row, instead of at the end of the column?

2) I noticed a weird thing: if you miss type a name in the textbox and try to go back using the backspace key (after a word has been autocompleted), it's doesn't delete the autocompleted word (I had to use the mouse to select the whole word, then the delete key worked). Any ideas?

Thanks,

Corey

 

[Mark O'Brien]

I told you it was just an example of autocomplete. I wasn't trying to tailor it to suit your needs.

I'll maybe try after dinner in an hour or two. It shouldn't be too difficult, bu tI'm far too light-headed right now to try.

 

[Mark O'Brien]

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:<pre>
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:<pre>
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.
_________________<font color = green> Mark O'Brien </font>

Columbus Ohio Celtic Supporters Club
This message was edited by Mark O'Brien on 2002-12-09 22:33

 

[Corman]

Hi Mark.

Thanks for taking the time to provide the help, I really appreciate it. That code works great (can use delete key now, etc.)

One strange thing I just noticed. The autocomplete is working, but only sporadically. For instance, in my column of data I have these company names:

Cormode and Dickson
Brytex Buildings
Bry Sand Concrete
Lauring Group
LJ projects Inc.

When I enter 'corm' into the textbox, it autocompletes the rest of cormode and dickson. It also works for Lauring Group.

But, when I enter 'lj projects', it doesn't autocomplete. Also, if I enter either 'bry s', or 'bryt', it doesn't complete those either.

I can't see any pattern as to why some would autocomplete but others wouldn't.

Another example:

abacus enterprises
abalon construction
abbarch partnership
abbey lane homes
abbey rd. communitas

If I type 'aba', nothing autocompletes (as it should be).
If I type 'abac', nothing autocompletes (but it should autocomplete 'abacus enterprises''.
If I type 'abbey', it does autocomplete, but with 'abbey rd. communitas'
If I type 'abbey l', it doesn't autocomplete 'abbey lane homes'.

I don't know if this makes a difference or not, but I have filters turned on so that, or instance, I can see all our clients that start with the letter 'C'. The first row of the excel sheet is the column headings/filters. The first line of actual data is on row 3.

Again, I really appeciate all this help. I'm finding that I like programming, and was wondering if you could recommended a good book on VB. I understand the basic parts of your coding, but much of it might as well be in french.

If you like, I could email you the file (although it is rather large, about 1mb right now).

I look forward to your response.

Corey

(you can also email a response directly to me at cdale_shelbyeng@hotmail.com)

 

[Corman]

Hmmmm...

I tried an experiment. I deleted all but the first 500 rows of data (deleted about 8000 rows!), and the autocomplete worked fine afterwards. I wonder if this is a bug in excel.

I then tried keeping the first 3000 rows, and it still worked fine.

Could there maybe be a limit to how many entries autocomplete can deal with?

Corey

 

[Matharu]

Hi Mark,
I was going through you code and wanted to ask is it possible for the text box autocomplete top lookup an access database for the suggestions instead of an excel worksheet?
Actually i'm creating an appliation in MS-Access and i'm using VBA. I want to implement this autocomplete feature in an access form.

I just need a help as to how to let this autocomplete refer the database...