ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,227
- Office Version
- 2007
- Platform
- Windows
Evening,
I have a working userform code shown below.
Currently i search for a customers name,its then shown in the listbox & if i select a name then the row in question is selected on my worksheet.
What i would like to do is after i select a customers name in the listbox is to add the text YES to its cell in column K
I will then continue to search & click customers names so certain cells in column K have the text YES in them.
My effort below of which some items are missing but stuck from here
I have a working userform code shown below.
Currently i search for a customers name,its then shown in the listbox & if i select a name then the row in question is selected on my worksheet.
What i would like to do is after i select a customers name in the listbox is to add the text YES to its cell in column K
I will then continue to search & click customers names so certain cells in column K have the text YES in them.
Rich (BB code):
Private Sub ListBox1_Click()
Range("B" & ListBox1.List(ListBox1.ListIndex, 1)).Select
End Sub
Private Sub CustomerSearch_Change()
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
.Clear
.ColumnCount = 3
.ColumnWidths = "260;220;0"
If CustomerSearch.Value = "" Then Exit Sub
Set r = Range("B8", Range("B" & Rows.Count).End(xlUp))
Set f = r.Find(CustomerSearch.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, 2) = f.Offset(, 2).Value
.List(i, 1) = f.Row
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value
.List(.ListCount - 1, 2) = f.Offset(, 2).Value
.List(.ListCount - 1, 1) = f.Row
End If
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
CustomerSearch = UCase(CustomerSearch)
.TopIndex = 0
Else
MsgBox "NO NAME WAS FOUND USING THAT INFORMATION", vbCritical, "PINK SEARCH"
CustomerSearch.Value = ""
CustomerSearch.SetFocus
End If
End With
End Sub
My effort below of which some items are missing but stuck from here
VBA Code:
Private Sub ListBox1_Click()
Range ("B" & ListBox1.List(ListBox1.ListIndex, 1))
With ThisWorkbook.Worksheets("POSTAGE")
.Cells(LastRow + 1, 11).Value = "YES"
End With
End Sub