ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,199
- Office Version
- 2007
- Platform
- Windows
Evening,
I am using the below code on a userform which consists of ListBox1.
When the code runs it looks in column G for the values shown in RED below
It then places these values in the ListBox1 like photo attached.
As time goes on this list will get larger / smaller BUT what ive noticed is that when the value LOST isnt in the list i then see the msg as shown in BLUE below.
I dont understand what is happening as the other values are present on the worksheet.
Not sure if this points to anything but the first entry has a thin border around it ?
Can you see where i went wrong Thanks
I am using the below code on a userform which consists of ListBox1.
When the code runs it looks in column G for the values shown in RED below
It then places these values in the ListBox1 like photo attached.
As time goes on this list will get larger / smaller BUT what ive noticed is that when the value LOST isnt in the list i then see the msg as shown in BLUE below.
I dont understand what is happening as the other values are present on the worksheet.
Not sure if this points to anything but the first entry has a thin border around it ?
Can you see where i went wrong Thanks
Rich (BB code):
Private Sub ListBox1_Click()
Range("A" & ListBox1.List(ListBox1.ListIndex, 4)).Select
Unload PostalIssueForm
End Sub
Private Function add_val(a As String)
Dim r As Range, f As Range, cell As String, added As Boolean
Dim sh As Worksheet
Set sh = Sheets("POSTAGE")
sh.Select
With ListBox1
.ColumnCount = 4
.ColumnWidths = "180;230;250;10"
Set r = Range("G8", Range("G" & Rows.Count).End(xlUp))
Set f = r.Find(a, 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 'POSTAL ISSUE COLUMN
.List(i, 1) = f.Offset(, -5).Value 'NAME
.List(i, 2) = f.Offset(, -4).Value 'ITEM
.List(i, 3) = f.Offset(, -6).Value 'DATE
.List(i, 4) = f.Row 'ROW
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value 'POSTAL ISSUE COLUMN
.List(.ListCount - 1, 1) = f.Offset(, -5).Value 'NAME
.List(.ListCount - 1, 2) = f.Offset(, -4).Value 'NAME
.List(.ListCount - 1, 3) = f.Offset(, -6).Value 'DATE
.List(.ListCount - 1, 4) = f.Row 'ROW
End If
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
ComboBox1 = UCase(ComboBox1)
.TopIndex = 0
Else
MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
ComboBox1.Value = ""
.SetFocus
End If
End With
End Function
Private Sub UserForm_Initialize()
Call add_val("LOST")
Call add_val("RECEIVED NO DATE")
Call add_val("RETURNED")
Call add_val("UNKNOWN")
End Sub