AutoComplete in VBA Forms? (Office 2000 SR1/VBA)

Topic

New Reply

Does anyone have any information on using IE’s autocomplete feature in a custom VBA form inside Word? Essentially, I’m trying to create a text box in a Userform that will keep track of previously entered information and then resolve any matching entries.

For example, let’s say a user enters a list of names… [Jane Doe, Robert Smith, Bob Jones]
The next time the user goes to the text box and enters “Jones”, I’d like the autocomplete feature to offer an option of using “Bob Jones”.

I have a method of doing this by storing the last x number of unique entries in the registry or in an INI file and checking the field on exit using the “LIKE” operator with wildcards, but IE’s autocomplete feature seems to be prospectively more elegant. Any tips?

Thanks!

Reply | Quote

Replies

Hubert, I have no experience with IE, so I cannot comment about trying to use it. However, if your application is Excel, then you can easily use Excel’s AutoComplete method for a range object in the change event. It’s a little tricky because your changing the textbox’s text will generate another change event.

Also, just found this code, Adding AutoComplete to a VB Text Box. It’s for VB not VBA, but it’s usually pretty simple to migrate. Let us know if it works! Sounds useful. –Sam
HTH –Sam

Reply | Quote

Just noticed a problem with the VB code in the link that I posted. It needs a window handle, hWnd. There is no such property in VBA, so you have to resort to more API. Assuming, that you are using a Userform, put a command button on the form and add the following code to see the window handle.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
                (ByVal lpClassName As String, _
                 ByVal lpWindowName As String) As Long

Private Sub CommandButton1_Click()
dim hWnd as Long
    hWnd = FindWindow(vbNullString, Me.Caption)
    MsgBox hWnd
End Sub

Reply | Quote

Thanks Sammy. I’ll try this out this afternoon and let you know how it goes.

Reply | Quote

Sammy–I tried out the code and the code seems to fire without error, but I don’t get any autocomplete suggestions. I must be missing something.

I assumed that hWnd should be set to me.Text1 and not me.caption, but I tried it both ways without success. I also wasn’t sure if my call to the SHAutocomplete function in VBNet’s code for the Command1 button should pass the constant SHACF_DEFAULT or if SHACF_AUTOSUGGEST_FORCE_ON or SHACF_AUTOAPPEND_FORCE_ON were more relevant. I also tried all permutations here but to no luck.

I started wondering–Do I need to store my entries somewhere in the registry and then have the textbox point to the stored entries in order for this to work? (sort of like how IE’s typed URLs are written to the reg).

Any clarification or suggestions would be greatly appreciated!

Thanks,
Hubert

[For clarification, my textbox is in a custom userform working in VBA for Word2000.]

Reply | Quote

> I assumed that hWnd should be set to me.Text1 and not me.caption, but I tried it both ways without success.

Do you get a value in the MsgBox for hWnd? It should be an integer (other than 0) that identifies the UserForm as a window in Windows’ big catalog of windows. It should work if you use the text that appears on the title bar of your UserForm. “Should,” but I haven’t tested it, and it’s possible that it’s not available in VBA.

Reply | Quote

Thanks Jefferson. Unfortunately, I do get a value for hWnd–and if I switch hWnd between me.Text1 and me.Caption, I get two different values–so I’m still guessing that the problem must be in the codes’ failure to explicitly read/write the values somewhere. Urrgh.

Reply | Quote

If Text1 is a control, you’re probably getting the hWnd for that control rather than for the application or form window.

Reply | Quote