Listbox Auto complete

[josros60]

I have listbox works fine, but would like to modify it by:

1. Instead of one click to double click in the name
2. Make list auto complete when start typing a letter to start scrolling to find name starting with that leter, i look in the property but cannot figure out.

here it's the code:

'Populate a multi-column combo and list box with values from spreadsheet'when the form is loaded
Private Sub UserForm_activate()
      Dim MyList(127, 5) 'as array type
      Dim R As Integer
      'The list box contains 3 data columns.
      'You can configure the number of columns, their width and height below
      'as well as change the text in the ControlTipText of the listbox
      Application.ShowToolTips = True
      With ListBox1
            .ColumnCount = 3
            .ColumnWidths = 90
            '.Width = 230
            '.Height = 110
            'ContactsInvoicing
            
            .ControlTipText = "Click the Customer, to send invoice to"
      End With
      'Define the list and where it's obtained from (Columns A, D, G in this example)
      With ActiveSheet
            'MyList (Row{0 to 9}, Column{0 to 4}) = the ranges given
            For R = 0 To 127
                  MyList(R, 0) = .Range("A" & R + 1)
                  MyList(R, 1) = .Range("b" & R + 1)
                  MyList(R, 2) = .Range("c" & R + 1)
                  MyList(R, 3) = .Range("d" & R + 1)
                  MyList(R, 4) = .Range("e" & R + 1)
            Next R
      End With
     'populate the list box
      ListBox1.List = MyList
End Sub
'When a name in the listbox is clicked, select the corresponding row
Private Sub listBox1_Click()
      Dim Customer  As Variant
      Dim Name As String
      Dim firstaddress As String
      
      
      Customer = Empty
      'If you add more than 500 names you will need to increase this
      With ActiveSheet.Range("a2:e1000")
            Name = ListBox1.Value
            Set Customer = .Find(What:=Name, LookIn:=xlValues)
            If Not Customer Is Nothing Then Customer.Rows.EntireRow.Select Else Exit Sub
      End With
      'closes the form when you click on a name
     ' Unload Me
     Dim ce As Range, i As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    Dim wksht As Worksheet
    Dim rw As Integer
    Dim rng As Range
    Dim HTMLbody As String
    
            Set OutApp = CreateObject("Outlook.Application")
            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)
          
          Set wksht = Worksheets("ContactsCollection")
          rw = ActiveCell.Row
          
                strto = wksht.Cells(rw, "c").Value
                strcc = "ar@hyperwallet.com"
                strbcc = wksht.Cells(rw, "H").Value
                strsub = wksht.Cells(rw, "D").Value
                strbody = "Hi" & " " & wksht.Cells(rw, "b").Value & ", " & vbCrLf & vbCrLf & "" & wksht.Cells(rw, "e").Value
                HTMLbody = RangetoHTML(rng)
                'End With
    
            With OutMail
                .To = strto
                .CC = strcc
                .BCC = strbcc
                .Subject = strsub
                .Body = strbody
                '.Send
                .display
                
            End With
    
            Set OutMail = Nothing
            Set OutApp = Nothing
            Set Customer = Nothing
         
With UserForm1
Unload Me
End With
[a1].Select
End Sub

Thanks

 

[sunnyland]

1. for the double click replace:
Private Sub listBox1_Click()
by
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

2.You need to modify the MatchEntry property:
0- match first letter - by pressing the same first letter again it will toggle between all name starting with the letter you type
1 is for a complete match meaning you will have to type in the full name for the selection to move to that name
2 do nothing - only select