ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,832
- Office Version
- 2007
- Platform
- Windows
Hi,
I use the code below to populate a listbox
The issue i have is that the row number isnt shown in the listbox.
Clicking on a listbox entry i am taken to that customer.
There are no run time error etc.
Only issue is that the row number isnt being shown.
Below is the code taken from one textbox.
Can you see an issue as to why this is.
Thanks
Private Sub TextBoxCUSTOMER_Change()
TextBoxCUSTOMER = UCase(TextBoxCUSTOMER)
Dim r As Range, f As Range, cell As String, added As Boolean
Dim sh As Worksheet
Set sh = Sheets("DETAILS")
sh.Select
With ListBox1
.Clear
.ColumnCount = 7
.ColumnWidths = "180;150;150;100;80;60;10"
If TextBoxCUSTOMER.Value = "" Then Exit Sub
Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set f = r.Find(TextBoxCUSTOMER.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(, 1).Value
.List(i, 2) = f.Offset(, 2).Value
.List(i, 3) = f.Offset(, 3).Value
.List(i, 4) = f.Offset(, 6).Value
.List(i, 5) = f.Offset(, 7).Value
.List(i, 7) = f.Row
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value
.List(.ListCount - 1, 1) = f.Offset(, 1).Value
.List(.ListCount - 1, 2) = f.Offset(, 2).Value
.List(.ListCount - 1, 3) = f.Offset(, 3).Value
.List(.ListCount - 1, 4) = f.Offset(, 6).Value
.List(.ListCount - 1, 5) = f.Offset(, 7).Value
.List(.ListCount - 1, 7) = f.Row
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", vbCritical, "CLONING INFORMATION MESSAGE"
TextBoxCUSTOMER.Value = ""
TextBoxCUSTOMER.SetFocus
End If
End With
End Sub
I use the code below to populate a listbox
The issue i have is that the row number isnt shown in the listbox.
Clicking on a listbox entry i am taken to that customer.
There are no run time error etc.
Only issue is that the row number isnt being shown.
Below is the code taken from one textbox.
Can you see an issue as to why this is.
Thanks
Private Sub TextBoxCUSTOMER_Change()
TextBoxCUSTOMER = UCase(TextBoxCUSTOMER)
Dim r As Range, f As Range, cell As String, added As Boolean
Dim sh As Worksheet
Set sh = Sheets("DETAILS")
sh.Select
With ListBox1
.Clear
.ColumnCount = 7
.ColumnWidths = "180;150;150;100;80;60;10"
If TextBoxCUSTOMER.Value = "" Then Exit Sub
Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set f = r.Find(TextBoxCUSTOMER.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(, 1).Value
.List(i, 2) = f.Offset(, 2).Value
.List(i, 3) = f.Offset(, 3).Value
.List(i, 4) = f.Offset(, 6).Value
.List(i, 5) = f.Offset(, 7).Value
.List(i, 7) = f.Row
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value
.List(.ListCount - 1, 1) = f.Offset(, 1).Value
.List(.ListCount - 1, 2) = f.Offset(, 2).Value
.List(.ListCount - 1, 3) = f.Offset(, 3).Value
.List(.ListCount - 1, 4) = f.Offset(, 6).Value
.List(.ListCount - 1, 5) = f.Offset(, 7).Value
.List(.ListCount - 1, 7) = f.Row
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", vbCritical, "CLONING INFORMATION MESSAGE"
TextBoxCUSTOMER.Value = ""
TextBoxCUSTOMER.SetFocus
End If
End With
End Sub
Rich (BB code):