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

Topic

New Reply

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?

Thanks,
Kim

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

fmMatchEntryFirstLetter	0	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.
fmMatchEntryComplete	1	Extended matching. As each character is typed, the control searches for an entry matching all characters entered (default).
fmMatchEntryNone	2	No matching.

From your description, it sounds as though “1” is the value you’d want to try.

Reply | Quote

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

Reply | Quote

Try this:

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

It’s also in the attached document.

Reply | Quote

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

You are wonderful!!!

Thanks, thanks, thanks,
Kim

Reply | Quote

Another possibility using the Dictionary.Scripting object :

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

Attached find your file with the above implemented.

Andrew C

Reply | Quote

Andrew! This works just great, too! And you, too, are wonderful!

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

Thanks
Kim

Reply | Quote

Just for completeness, I ishould have included the following :

    Private Sub UserForm_Terminate()
        Set Dict = Nothing
    End Sub

Andrew C

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Just for completeness, I ishould have included the following :

    Private Sub UserForm_Terminate()
        Set Dict = Nothing
    End Sub

Andrew C

Reply | Quote

Andrew! This works just great, too! And you, too, are wonderful!

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

Thanks
Kim

Reply | Quote

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.

Reply | Quote

Good point. No Match could be handled by selecting nothing as with the following modification

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

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

Andrew

Reply | Quote

Good point. No Match could be handled by selecting nothing as with the following modification

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

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

Andrew

Reply | Quote

Maybe this modification works for best match :

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

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.

    Private Sub ListBox1_DblClick(ByVal Cancel _
                As MSForms.ReturnBoolean)
        TextBox1.Text = ListBox1.Text
        TextBox1.SetFocus
    End Sub

Andrew C

Reply | Quote

> Another possible useful facility woul be to transfer the List box item to the text box with a double click.

As long as we’re redesigning Kim’s software, I thought transferring it by pressing Enter in the textbox would be good, too. I haven’t done very much _KeyUp programming, but that seems like a good place to put such code.

Reply | Quote

I’m enchanted by the work you guys are doing on my software. It’s very enlightening and I’m most grateful.

A note on Andrew’s suggestion regarding “Best Match”

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

worked great.

A note of value about using the Dictionary object is that it is case sensitive. If you start typing “v” in the textbox, knowing you have a “Vincent” in your list, the listbox will not respond because “Vincent” begins with an uppercase “V.” Maybe this could be managed with the LCase function — at least on the first letter of each entry. In the meantime, I’m happy with using the Shift key.

Thanks!
Kim

Reply | Quote

> Dictionary object is that it is case sensitive

If you include the following line in red, that problem should be taken care of :

    Set Dict = New Scripting.Dictionary
    Dict.CompareMode = DatabaseCompare

Jefferson’s idea can be implemented with

    Private Sub TextBox1_KeyUp(ByVal KeyCode _
            As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 13 Then
            TextBox1 = ListBox1
        End If
    End Sub

and you could add further code to possibly dismiss the UserForm.

We all learn a bit from dabbling in these code snippets, and I imight find use for this myself.

Andrew C

Reply | Quote

> Dict.CompareMode = DatabaseCompare

As a non-Access programmer, I’d probably use TextCompare or vbTextCompare (either = 1) myself, but… whatever works!

Reply | Quote

Both seem to work just fine, (BinaryCompare is the default, which does not suit in this case), but you arecorrect, TextCompare is the most logical. My use of DatabaseCompare results from some recent work with Access and I forgot where I was.

Thanks,

Reply | Quote

Both seem to work just fine, (BinaryCompare is the default, which does not suit in this case), but you arecorrect, TextCompare is the most logical. My use of DatabaseCompare results from some recent work with Access and I forgot where I was.

Thanks,

Reply | Quote

Thanks guys,

TextCompare did the trick. DatabaseCompare only works in MSoft Access (according to the MSDN Library).

This is such a nifty addition to my project, you have no idea. It’s a tiny part of the whole, but it makes all the difference — really looks and feels more professional.

Group hug!

Kim

Reply | Quote

Thanks guys,

TextCompare did the trick. DatabaseCompare only works in MSoft Access (according to the MSDN Library).

This is such a nifty addition to my project, you have no idea. It’s a tiny part of the whole, but it makes all the difference — really looks and feels more professional.

Group hug!

Kim

Reply | Quote

> Dict.CompareMode = DatabaseCompare

As a non-Access programmer, I’d probably use TextCompare or vbTextCompare (either = 1) myself, but… whatever works!

Reply | Quote

> Dictionary object is that it is case sensitive

If you include the following line in red, that problem should be taken care of :

    Set Dict = New Scripting.Dictionary
    Dict.CompareMode = DatabaseCompare

Jefferson’s idea can be implemented with

    Private Sub TextBox1_KeyUp(ByVal KeyCode _
            As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 13 Then
            TextBox1 = ListBox1
        End If
    End Sub

and you could add further code to possibly dismiss the UserForm.

We all learn a bit from dabbling in these code snippets, and I imight find use for this myself.

Andrew C

Reply | Quote

I’m enchanted by the work you guys are doing on my software. It’s very enlightening and I’m most grateful.

A note on Andrew’s suggestion regarding “Best Match”

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

worked great.

A note of value about using the Dictionary object is that it is case sensitive. If you start typing “v” in the textbox, knowing you have a “Vincent” in your list, the listbox will not respond because “Vincent” begins with an uppercase “V.” Maybe this could be managed with the LCase function — at least on the first letter of each entry. In the meantime, I’m happy with using the Shift key.

Thanks!
Kim

Reply | Quote

> Another possible useful facility woul be to transfer the List box item to the text box with a double click.

As long as we’re redesigning Kim’s software, I thought transferring it by pressing Enter in the textbox would be good, too. I haven’t done very much _KeyUp programming, but that seems like a good place to put such code.

Reply | Quote

Maybe this modification works for best match :

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

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.

    Private Sub ListBox1_DblClick(ByVal Cancel _
                As MSForms.ReturnBoolean)
        TextBox1.Text = ListBox1.Text
        TextBox1.SetFocus
    End Sub

Andrew C

Reply | Quote

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.

Reply | Quote

Another possibility using the Dictionary.Scripting object :

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

Attached find your file with the above implemented.

Andrew C

Reply | Quote

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

You are wonderful!!!

Thanks, thanks, thanks,
Kim

Reply | Quote

Try this:

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

It’s also in the attached document.

Reply | Quote

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

Reply | Quote

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

fmMatchEntryFirstLetter	0	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.
fmMatchEntryComplete	1	Extended matching. As each character is typed, the control searches for an entry matching all characters entered (default).
fmMatchEntryNone	2	No matching.

From your description, it sounds as though “1” is the value you’d want to try.

Reply | Quote