Type in TextBox to show values in ListBox advice

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Morning,
I have UserForm1 of which consists of TextBox1 & ListBox1

I wish to start typing in TextBox1 & the matching items are then shown in ListBox1

Some info for you.
The database values are in the same workbook & are on worksheet INFO
They are in Table 24 or the range is CR2:CR86 but values will be added in time making it then CR87, CR88, CR89 etc etc

I have started with the below but then got lost / confused

Rich (BB code):
    Private Sub TextBox1_Change()
      Dim r As Range, f As Range, cell As String, added As Boolean
      Dim sh As Worksheet
      
      Set sh = Sheets("INFO")
      sh.Select
      With ListBox1
        .Clear
        .ColumnCount = 3
        .ColumnWidths = "280;150;70"
        If TextBox1.Value = "" Then Exit Sub
        Set r = Range("CR2", Range("CR" & Rows.Count).End(xlUp))
        Set f = r.Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlPart)
        If Not f Is Nothing Then
          cell = f.Address
          Do
            added = False
            For i = 0 To .ListCount - 1
              Select Case StrComp(.List(i), f.Value, vbTextCompare)
                Case 0, 1
                  .AddItem f.Value, i                 'col B
                  .List(i, 1) = f.Offset(, -1).Value  'col A
                  added = True
                  Exit For
              End Select
            Next
            If added = False Then
              .AddItem f.Value                                 'col B
              .List(.ListCount - 1, 1) = f.Offset(, -1).Value  'col A
            End If
            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
          TextBox1 = UCase(TextBox1)
          .TopIndex = 0
        Else
          MsgBox "NO NUMBER WAS FOUND USING THAT INFORMATION", vbCritical, "HONDA PART NUMBER SEARCH"
          TextBox1.Value = ""
          TextBox1.SetFocus
        End If
      End With
    End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Thanks yes i did

Would you mind taking a look at this.

 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,118,889
Messages
5,574,847
Members
412,620
Latest member
sharma7s
Top