Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Eugene, Oregon, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Eugene, Oregon, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Eugene, Oregon, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.]

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

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

    > 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.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Eugene, Oregon, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •