ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,251
- Office Version
- 2007
- Platform
- 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
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