List pops up and sorts it self when inserting first word. Hi, Could it be possib

Question

List pops up and sorts it self when inserting first word.

Hi,

Could it be possible, lets say I have a list of suppliers on one sheet and on the other I can post transactions to it so I can use the data validation list to chose which supplier I would be posting that transaction to. But the problem is when you have a lot of suppliers it's a hassle on searching the data validation list on finding that supplier.

So could it be possible so that when typing that supplier's name while typing a small pop box pops up a list like a data validation and shows me the suppliers I have available with that name.

For example

These are on another sheet called "Creditors List"

List Of Suppliers

Applecore

Applefoods

Rimus

J.Calleja

Real Food

So when typing the supplier on another sheet called "Cash & Chq Payments History" in column D for each and every cell starting from Row 9, but lets say I am on Cell D10. So I type: app only so when typing that a list pops up and shows me the two available suppliers Applecore, Appelfoods and then I could chose one of them.

Thanks

Explanation / Answer

You can do this, but it requires using a userform (Userform1) with a TextBox (TextBox1) and a ListBox (ListBox1). As you type in the textbox, the list the listbox will be filtered, at which point if the value is unique it will be entered into the cell, or you can choose it from the listbox to enter it into the cell. Layout the userform so that the elements are wide enough to show the full value of the names.

You will need to create a named range "NameList" that includes all your suppliers' names. (You can make the range dynamic so that it expands as you add suppliers.)

Then use this code in the code module of the sheet "Cash & Chq Payments History"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("D9:D60000")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
UserForm1.Show
Unload Me
End Sub

And use this code in the codemodule of the userform:

Private Sub ListBox1_Change()
Application.EnableEvents = False
ActiveCell.Value = Me.ListBox1.List(Me.ListBox1.ListIndex)
Application.EnableEvents = True
Unload Me
End Sub

Private Sub TextBox1_Change()
Me.ListBox1.List = Range("NameList").Value
For i = Me.ListBox1.ListCount To 1 Step -1
If Not UCase(Me.ListBox1.List(i - 1)) Like UCase(Me.TextBox1.Value & "*") Then
Me.ListBox1.RemoveItem i - 1
End If
Next i
If Me.ListBox1.ListCount = 1 Then
Application.EnableEvents = False
ActiveCell.Value = Me.ListBox1.List(0)
Application.EnableEvents = True
Unload Me
End If
End Sub

Private Sub UserForm_Initialize()
Me.ListBox1.List = Range("NameList").Value
End Sub

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.