Get selection from ListBox and copy to TextBox (USERFORM)

[nordicdust]

Happy New Year
And Thank you for taking a look at my problem.

What i have:
A Userform where I type a zip code in the textBox and while typing the ListBox shows the matching possibilities from a Range.
The Range is merged cells from a Table where I have Zip code in Col 1 and City in Col 2.
I can only search by ZipCode.

Example workBook Uploaded.



What I relly would like:
- A Userform where I would like a way to type in the textBox while the ListBox shows the values from a Table with Zip code in Col 1 and City in Col 2.
- While typing in TextBox the ListBox should only show matching possibilities.
- I would like the option of searching by either Zip Code or City.
- The ListBox should show Zip Code and Matching City (As in merged Cells on Sheet)
- A way to Select the value from the ListBox to the TextBox.

Any Help is much appreciated


A Snip of my code so far:

Private Sub Txb_POSTNR_BY_Change()

Dim a
Dim firstAddress As String
Me.Listbox_RESULTAT.Clear

With ThisWorkbook.Worksheets("ENGINE").Range("Q2:Q159")
Set a = .Find(Txb_POSTNR_BY.Text, LookIn:=xlValues, LookAt:=xlPart)
If Not a Is Nothing Then
firstAddress = a.Address

Do
    Me.Listbox_RESULTAT.AddItem a.Text
        Set a = .FindNext(a)
            Loop While Not a Is Nothing And a.Address <> firstAddress
            Else
        MsgBox "No Match Found"
    End If
End With

End Sub

Private Sub UserForm_Initialize()
Listbox_RESULTAT.List = ThisWorkbook.Worksheets("ENGINE").Range("Q2:Q159").Value
End Sub

Udklip.PNG

[Akuini]

What I relly would like:
- A Userform where I would like a way to type in the textBox while the ListBox shows the values from a Table with Zip code in Col 1 and City in Col 2.
- While typing in TextBox the ListBox should only show matching possibilities.
- I would like the option of searching by either Zip Code or City.
- The ListBox should show Zip Code and Matching City (As in merged Cells on Sheet)
- A way to Select the value from the ListBox to the TextBox.

Try this:
Notes:
1. searching in textbox is case insensitive, try typing '72 a'.
2. to transfer value from listbox to textbox use doubleclick.

Option Explicit
Public flag As Boolean

Private Sub Cmbt_OK_Click()
    
    Unload UF_INDTASTSAGSINFORMATION
    
End Sub

Private Sub Listbox_RESULTAT_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
flag = True
Txb_POSTNR_BY.Value = Me.Listbox_RESULTAT.Value

End Sub

Private Sub Txb_POSTNR_BY_Change()

Dim a, vList
Dim firstAddress As String
Dim d As Object, i As Long
If flag = True Then
    flag = False
    Exit Sub
End If

vList = ThisWorkbook.Worksheets("ENGINE").Range("Q2:Q159").Value
If Txb_POSTNR_BY.Value <> "" Then

    Set d = CreateObject("scripting.dictionary")
        For i = LBound(vList) To UBound(vList)
            If LCase(vList(i, 1)) Like "*" & Replace(LCase(Txb_POSTNR_BY.Value), " ", "*") & "*" Then
              d(vList(i, 1)) = 1
            End If
        Next
       Me.Listbox_RESULTAT.List = d.keys
       If d.Count = 0 Then MsgBox "No Match Found"
    Else
    Me.Listbox_RESULTAT.List = vList
    
End If

End Sub

Private Sub UserForm_Initialize()
Listbox_RESULTAT.List = ThisWorkbook.Worksheets("ENGINE").Range("Q2:Q159").Value
flag = False
End Sub

[nordicdust]

WOW Akuini

That is absolutly Perfect :-)
This will make my day so much much easier at work.

I Try to use the keyboard as much as possible in my workbooks and less Mouse.

When there is like 4 options in the ListBox, if I press the Key Down, the options turn blue (Marked)
Is it possible to Set TextBox = The marked option in ListBox ? Then I could just press Tap to go to OK.

I hope this makes sence :-)

@Akuini
I just thought that maybe the best solution was

if d.Count = 1 Then Txb_POSTNR_BY.Value = Me.Listbox_RESULTAT.Value

Is that possible?

[Akuini]

Ok, see if this suits your needs.
Steps:
1. The user types something in the text box
2. press tab > focus will go to the listbox
3. use the up\down arrow > press Enter > textbox value = listbox value
4. press tab > focus will go to the GO button

Add these lines to the existing code:

Private Sub Listbox_RESULTAT_Enter()
Me.Listbox_RESULTAT.ListIndex = 0
End Sub
Private Sub Listbox_RESULTAT_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.Listbox_RESULTAT.ListIndex = -1
End Sub
Private Sub Listbox_RESULTAT_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
flag = True
Txb_POSTNR_BY.Value = Me.Listbox_RESULTAT.Value

End Sub

[nordicdust]

Thank you Akuini

This is perfect, I will mark this as Solved.

[Akuini]

Thank you Akuini

This is perfect, I will mark this as Solved.

You're welcome, glad to help.
I modify the code a bit to limit the keypress (in the listbox) to Enter key.

Private Sub Listbox_RESULTAT_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii = 13 Then
        flag = True
        Txb_POSTNR_BY.Value = Me.Listbox_RESULTAT.Value
        'MsgBox Txb_POSTNR_BY.Value
    End If
End Sub

Just a thought:
I don't know what the OK button does, but to reduce the steps you need to take, you can just move the code (for your OK button) to the 'Private Sub Listbox_RESULTAT_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)'. That way you won't need the OK button anymore.
For example:
In the modified code above I add :

'MsgBox Txb_POSTNR_BY.Value

comment out the line to see what happen. I mean you can put in there the code to do what your OK button do .