Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    In Word, I have created a UserForm with a listbox filled with contacts from Outlook. I would like to add a text box (or other likely control) to look up contacts in the list. That is, I would like it to simulate typing while the listbox has focus. So, in the text box, one would type "L" and the selection in the listbox would move to the first item in the list beginning with L. Then one would type "i" and the selection in the listbox would move to the first item beginning with "Li" and so on.

    There are a few places in Office where you can do this -- if you use the GetAddress method, it displays a dialog that allows you to type in a box while the selection bar in the listbox jumps to the name you are typing. I would like to simulate that.

    Yes, I could just let the users type a name in a text box and click Find. Or I could let them move focus back to the list (heck, I could move focus back to the list for them). But a lookup is more user-friendly and consistent with what they will find elsewhere.

    I've tried several ways (most promising and most erratic being SendKeys) and have not been able to resolve.

    Does anyone have any ideas? Pleeeeze? <img src=/S/please.gif border=0 alt=please width=31 height=23>

    Thanks,
    Kim

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    In the article Search Listboxes as you Type a modified SendMessage call is used to achieve the desired effect. I don't know if the code here could be modified for use in VBA, which doesn't support handles for controls.

    Another idea, which might also be slow though, is to capture the keystrokes to the textbox and rebuild the list after each new letter appears (similar to filtering I suppose).

    Alan

  3. #3
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    Hi Alan,

    While this is spiffy code, unfortunately it doesn't work for VBA. This has been my biggest hangup -- there doesn't seem to be a way to pass each keystroke to the listbox control as you're typing it. I had brief success with SendKeys, but anything after two keystrokes would cause a big pause, then a bunch of keystrokes (duplicates and triplicates of the ones I'd typed) would appear in the text box.

    I have attached a document with the SendKeys code. I believe I got the basis for this from the MSDN Library and it, too used a method not supported by VBA. (SelectByValue)

    Thanks,
    Kim

  4. #4
    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: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    A VBA listbox has a property that lets you type-to-select. Have you tried this and found it insufficient for your purposes? (You might need to add a "clear" button if you want the user to have the option to have nothing selected.)

    MatchEntry Property

    <table border=1 bordercolor=gray cellspacing=0 cellpadding=5 bgcolor=lightblue><td>fmMatchEntryFirstLetter</td><td>0</td><td>Basic matching. The control searches for the next entry that starts with the character entered. Repeatedly typing the same letter cycles through all entries beginning with that letter.</td><td>fmMatchEntryComplete</td><td>1</td><td>Extended matching. As each character is typed, the control searches for an entry matching all characters entered (default). </td><td>fmMatchEntryNone</td><td>2</td><td>No matching.</td></table>
    From your description, it sounds as though "1" is the value you'd want to try.

  5. #5
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    Hi J,

    I do have the MatchEntry property set to 1. However, as far as I can tell, you can only use it while focus is in the Listbox. I want the user to be able to type in the Textbox and have the Listbox respond. It's way more user-friendly than hoping the user understands that they need to type (or use the arrow keys) if they're just plunked down in a listbox.

    There may be no solution to my problem without resorting to VB -- I'd rather not do that given the logistics. If that's the case, I'll just have the Textbox gather the user's typing, then they'll click a Find button. I can SendKeys to the listbox and have it select the typed name. If they've typed the name wrong, the listbox will display the closest match, so it's a pretty good second choice.

    Still, how cool would it be to have the lookup be "live"?

    Thanks,
    K

  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: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    Try this:
    <pre>Private strLast As String

    Private Sub TextBox1_Change()
    Dim strNextItem As String, intNextItem As Integer
    If Len(Me.TextBox1.Value) = 0 Then
    With Me.ListBox1
    .Selected(0) = True
    .ListIndex = -1
    .Selected(0) = False
    End With
    ElseIf Len(Me.TextBox1.Value) > Len(strLast) Then
    ' Try to match downwards in the listbox
    With Me.ListBox1
    If IsNull(.Value) Then
    strNextItem = " "
    Else
    strNextItem = .Value
    End If
    If StrComp(Left(strNextItem, Len(Me.TextBox1.Value)), _
    Me.TextBox1.Value, vbTextCompare) = 1 Then GoTo bye
    Do
    intNextItem = .ListIndex + 1
    If intNextItem >= .ListCount Then Exit Do
    strNextItem = .List(intNextItem)
    Select Case StrComp(Left(strNextItem, Len(Me.TextBox1.Value)), _
    Me.TextBox1.Value, vbTextCompare)
    Case -1 ' Not quite a match, but might be the best shot
    .ListIndex = intNextItem
    .Selected(.ListIndex) = True
    Case 0 ' Perfect match
    .ListIndex = intNextItem
    .Selected(.ListIndex) = True
    Exit Do
    Case Else ' No better match
    Exit Do
    End Select
    Loop
    End With
    ElseIf Len(Me.TextBox1.Value) < Len(strLast) Then
    ' Try to match upwards in the listbox
    With Me.ListBox1
    strNextItem = .Value
    Do
    intNextItem = .ListIndex - 1
    If intNextItem < 0 Then Exit Do
    strNextItem = .List(intNextItem)
    Select Case StrComp(Left(strNextItem, Len(Me.TextBox1.Value)), _
    Me.TextBox1.Value, vbTextCompare)
    Case 0 ' Perfect match, but check above just in case
    .ListIndex = intNextItem
    .Selected(.ListIndex) = True
    Case 1 ' Not quite a match, but might be the best shot
    .ListIndex = intNextItem
    .Selected(.ListIndex) = True
    Case Else ' No better match, stay put
    Exit Do
    End Select
    Loop
    End With
    End If
    bye:
    strLast = Me.TextBox1.Value
    End Sub</pre>

    It's also in the attached document.

  7. #7
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    YES!!! I just ran the demo in your document and THIS IS WHAT I'VE BEEN LOOKING FOR!

    You are wonderful!!! <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>

    Thanks, thanks, thanks,
    Kim

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    Another possibility using the Dictionary.Scripting object :<pre>Option Explicit
    Public Dict As Scripting.Dictionary

    Private Sub TextBox1_Change()
    Me.ListBox1.ListIndex = Dict.item(TextBox1.Text)
    End Sub

    Private Sub UserForm_Initialize()

    Set Dict = New Scripting.Dictionary
    Move 0, 0, 570, 380

    TextBox1.Move 30, 40, 220, 160
    TextBox1.MultiLine = True
    TextBox1.WordWrap = True
    TextBox1.Text = ""
    TextBox1.EnterKeyBehavior = True

    ListBox1.Move 298, 40, 220, 160

    AddListItem Me.ListBox1, "aaaaaa"
    AddListItem Me.ListBox1, "abbbbb"
    AddListItem Me.ListBox1, "abcccc"
    AddListItem Me.ListBox1, "abcddd"
    AddListItem Me.ListBox1, "abcdee"
    AddListItem Me.ListBox1, "abcdef"

    TextBox1.SetFocus

    End Sub

    Sub AddListItem(lb As ListBox, strItem As String)
    Dim item As Long, i As Integer
    item = lb.ListCount
    lb.AddItem strItem
    With Dict
    For i = 1 To Len(strItem)
    On Error Resume Next
    .Add Left(strItem, i), item
    Next
    End With
    End Sub</pre>


    Attached find your file with the above implemented.

    Andrew C

  9. #9
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    Andrew! This works just great, too! And you, too, are wonderful! <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>

    I went from SOL to up to my hips in solutions. You guys are great!

    Thanks <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Kim

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    Just for completeness, I ishould have included the following :<pre> Private Sub UserForm_Terminate()
    Set Dict = Nothing
    End Sub</pre>

    Andrew C

  11. #11
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    And (just to make sure I saw this correctly) you added a reference to Microsoft Scripting runtime. Are there any potential pitfalls with that in a Windows XP / Office XP environment?

    AND by the way...

    These wonderful solutions don't work when something hinky is going on with Outlook. I will post this to the Outlook forum if that's appropriate, but...

    As a result of having code that actually works, I have found that my FileAs settings in Outlook are NOT sticking. I just went through every contact I have and at least two or three are still out of whack after I thought I fixed them. This manifests itself by having a name out of order. For example, "Smith, Diane" is stuck in the middle of the "C"s when it should be filed by (and is indeed being alphabetized by) the company name -- which begins with a C.

    When I look at the offending contact, in the address card view, it appears correctly and correctly alphabetized by the Company Name and followed by the Contact Name on the second line. However, when I look at the record, I see that only the contact name appears in the FileAs field. I have even re-set the FileAs field, clicked Save and Close, and gone back in immediately and found the FileAs reset to the name only.

    There is no code running on the form in Outlook. Could the form be corrupt? I have compacted the pst file -- is there anything else to do?

    Thanks,
    Kim

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    Kim,

    Sorry for not mentioning the reference to the Scripting Runtime. There should not be any pitfalls with XP. In fact if your system does not already have the Scripting Runtime installed (unlikely), it is installed as part of the Office XP installation.

    I'm sorry but I cannot shed any light on you Outlook problems, but perhaps Jefferson or one of our other Outlook experts will assist.

    Andrew

  13. #13
    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: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    This is fast, but if you enter nonexistent keys, such as abz it behaves differently than Windows listboxes. Instead of "best match" it drops back to ListIndex=0. I'm sure it would be possible to code around that, but it might be more trouble than it's worth.

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    Good point. No Match could be handled by selecting nothing as with the following modification <pre>Private Sub TextBox1_Change()
    If Dict.item(TextBox1.Text) = "" Then
    Me.ListBox1.ListIndex = -1
    Else
    Me.ListBox1.ListIndex = Dict.item(TextBox1.Text)
    End If
    End Sub</pre>

    As you suggest, Best Match might be more trouble than is worth, unless one is up for a challenge. Another time perhaps.

    Andrew

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup textbox for a listbox (VBA / Word 2002 / SP-2)

    Maybe this modification works for best match :<pre> Private Sub TextBox1_Change()
    If Dict.item(TextBox1.Text) <> "" Then
    Me.ListBox1.ListIndex = Dict.item(TextBox1.Text)
    End If
    End Sub</pre>

    It should leave the selection at the last match (if any).

    Another possible useful facility woul be to transfer the List box item to the text box with a double click.<pre> Private Sub ListBox1_DblClick(ByVal Cancel _
    As MSForms.ReturnBoolean)
    TextBox1.Text = ListBox1.Text
    TextBox1.SetFocus
    End Sub</pre>

    Andrew C

Page 1 of 2 12 LastLast

Posting Permissions

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