Listbox Search Function
 

Hi,

I'm in over my head as an amateur, but I'm trying to allow a user to enter
data and search for a customer name. That part works, but now if the name
isn't found I want to filter the data by the first letter and create a
listbox that will allow the user to select one of the available names that
begins with the first letter of their entry.

Here's the code I've written. It executes without error, but nothing seems
to happen (data isn't copied).

Please help!

Msg = sourceRange.Value & " Not Found. Would you like to lookup?" ' Define
message.
Style = vbYesNo ' Define buttons.
Title = "Customer Not Found" ' Define title.

Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then GoTo ender ' User chose Yes.

SEARCH:
SearchLtr = sourceRange
If SearchLtr = "" Then MsgBox ("Please Enter a Customer Name")
Exit Sub 'Have to enter something in inputbox
End If

sourceWB.Activate
If Len(SearchLtr) 1 Then SearchLtr = Left(SearchLtr, 1)
Range("d1").Select
Selection.AutoFilter Field:=1, Criteria1:=UCase(SearchLtr),
Operator:=xlOr, _
Criteria2:=LCase(SearchLtr) 'Autofilter by upper case or lower case of
letter entered
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("CL2").Select
ActiveSheet.Paste



'Sort the new non dupe list
Range("c:c").EntireColumn.Select
Selection.Sort Key1:=Range("CL2:CL65536"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom



rowend = Selection.Count.xlendrow

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 100, rowend, 100, 100)
For x = 1 To rowend
lb.ControlFormat.AddItem x
Next
End With