ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Morning,
I am using the code shown below.
Currently i start to type a customer in TextBox1 and the results are shown in ListBox1 opposit.
Once i see the customer i require i select it in the Listbox results,the userform closes & that customer is then selected on my worksheet.
I now need an option to be able to delete a customer on the worksheet but using the same search / show option as currently in use.
I dont mind having another userform "so i can just copy this one" BUT when i select the customer it would then DELETE it from the worksheet as opposed to select it.
Once that is in place i can then start to add my own msgbox etc etc but lost as to how i edit the select name etc etc for delete customer
many thanks
I am using the code shown below.
Currently i start to type a customer in TextBox1 and the results are shown in ListBox1 opposit.
Once i see the customer i require i select it in the Listbox results,the userform closes & that customer is then selected on my worksheet.
I now need an option to be able to delete a customer on the worksheet but using the same search / show option as currently in use.
I dont mind having another userform "so i can just copy this one" BUT when i select the customer it would then DELETE it from the worksheet as opposed to select it.
Once that is in place i can then start to add my own msgbox etc etc but lost as to how i edit the select name etc etc for delete customer
many thanks
VBA Code:
Private Sub ClearButton_Click()
TextBox1.Value = ""
TextBox1.SetFocus
End Sub
Private Sub CloseButton_Click()
Unload HondaListCustomerSearch
End Sub
Private Sub ListBox1_Click()
Set sh = Sheets("HONDA LIST")
sh.Select
Range("C" & ListBox1.List(ListBox1.ListIndex, 3)).Select
Unload HondaListCustomerSearch
End Sub
Private Sub TextBox1_Change()
Dim r As Range, f As Range, cell As String, added As Boolean
Dim sh As Worksheet
Set sh = Sheets("HONDA LIST")
sh.Select
With ListBox1
.Clear
.ColumnCount = 4
.ColumnWidths = "240;100;280;50"
If TextBox1.Value = "" Then Exit Sub
Set r = Range("C4", Range("C" & 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 'Item
.List(i, 1) = f.Offset(, 4).Text 'Date
.List(i, 3) = f.Row 'Row Number
.List(i, 2) = f.Offset(, -1).Value 'Customers Name
added = True
Exit For
End Select
Next
If added = False Then
.AddItem f.Value 'Item
.List(.ListCount - 1, 1) = f.Offset(, 4).Text 'Date
.List(.ListCount - 1, 3) = f.Row 'Row Number
.List(.ListCount - 1, 2) = f.Offset(, -1).Value 'Customer Name
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 CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
TextBox1.Value = ""
TextBox1.SetFocus
End If
End With
End Sub