ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,232
- Office Version
- 2007
- Platform
- Windows
Hi,
I am using the code supplied below.
I also attach a screenshot of the issue.
I open the userform & in the TextBox Chassis Number i enter JMA
The results are they shown in the ListBox BUT with an issue.
As you can see that there is a blank space to the right of the row 54 number.
Looking at the screenshot you will see that there is a name in column A.
I can see this happen a few times when entering a different value.
Can you see why this might be happening.
Thanks
I am using the code supplied below.
I also attach a screenshot of the issue.
I open the userform & in the TextBox Chassis Number i enter JMA
The results are they shown in the ListBox BUT with an issue.
As you can see that there is a blank space to the right of the row 54 number.
Looking at the screenshot you will see that there is a name in column A.
I can see this happen a few times when entering a different value.
Can you see why this might be happening.
Thanks
Rich (BB code):
Private Sub TextBoxChassisNumber_Change()
TextBoxChassisNumber = UCase(TextBoxChassisNumber)
Dim r As Range, f As Range, cell As String, added As Boolean
Dim sh As Worksheet
Set sh = Sheets("DATABASE")
sh.Select
With ListBox1
.Clear
.ColumnCount = 8
.ColumnWidths = "180;240;80;180"
If TextBoxChassisNumber.Value = "" Then Exit Sub
Set r = Range("L6", Range("L" & Rows.Count).End(xlUp))
Set f = r.Find(TextBoxChassisNumber.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
.List(i, 1) = f.Offset(, -10).Value
.List(i, 2) = f.Row
.List(i, 3) = f.Offset(, -11).Value
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value
.List(.ListCount - 1, 1) = f.Offset(, -10).Value
.List(.ListCount - 1, 2) = f.Row
.List(.ListCount - 1, 3) = f.Offset(, 113).Value
End If
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
TextBoxSearch = UCase(TextBoxSearch)
.TopIndex = 0
Else
MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
TextBoxChassisNumber.Value = ""
TextBoxChassisNumber.SetFocus
End If
End With
End Sub