Alternative to Data Validation (List)
 

Good day all.
Excel 2007 on XP
I am using a data validation list in a cell in order to select a part number.
However, as the parts list grows (now approx. 2000) it becomes impractical
to run through the dropdown listing box to select.
Anyone aware of an alternative (more user friendly) method to use either
with data validation or using a macro?

Alternative to Data Validation (List)
 

a userform with a textbox and two listboxe
you can use the textbox as a filter for the listbox

the first listbox, listbox1, holds ALL the data and its visible property is
FALSe, so you can see it.
Load the 2nd listbox from the first.
when a value is typed into the textbox, clear liistbox2 and repopulate from
listbox1, but only items that match the filter

Option Explicit
Private Sub TextBox1_Change()
ListBox2.Clear
Dim index As Long
With ListBox1
For index = 0 To .ListCount - 1
If .List(index) Like "*" & TextBox1.Text & "*" Then
ListBox2.AddItem .List(index)
End If
Next
End With
End Sub
Private Sub UserForm_Initialize()
TextBox1_Change
End Sub

I have a slowing PC, and for 2,500 items, the speed I found accepable

"PeterH" wrote:

Good day all.
Excel 2007 on XP
I am using a data validation list in a cell in order to select a part number.
However, as the parts list grows (now approx. 2000) it becomes impractical
to run through the dropdown listing box to select.
Anyone aware of an alternative (more user friendly) method to use either
with data validation or using a macro?

Alternative to Data Validation (List)
 

Thanks Partick,
Please forgive the stupid question but I am not familiar with the userform,
textbox or listboxes - how do I set this up?

"Patrick Molloy" wrote:

a userform with a textbox and two listboxe
you can use the textbox as a filter for the listbox

the first listbox, listbox1, holds ALL the data and its visible property is
FALSe, so you can see it.
Load the 2nd listbox from the first.
when a value is typed into the textbox, clear liistbox2 and repopulate from
listbox1, but only items that match the filter

Option Explicit
Private Sub TextBox1_Change()
ListBox2.Clear
Dim index As Long
With ListBox1
For index = 0 To .ListCount - 1
If .List(index) Like "*" & TextBox1.Text & "*" Then
ListBox2.AddItem .List(index)
End If
Next
End With
End Sub
Private Sub UserForm_Initialize()
TextBox1_Change
End Sub

I have a slowing PC, and for 2,500 items, the speed I found accepable

"PeterH" wrote:

Good day all.
Excel 2007 on XP
I am using a data validation list in a cell in order to select a part number.
However, as the parts list grows (now approx. 2000) it becomes impractical
to run through the dropdown listing box to select.
Anyone aware of an alternative (more user friendly) method to use either
with data validation or using a macro?

Alternative to Data Validation (List)
 

Hi Peter
You will need to do some VBA, check these sites for some information.
http://www.contextures.com/xlUserForm01.html
http://www.excel-vba.com/vba-forms-3-1-creating.htm
Just Google UserForm in Excel, you will get all sort of help.
HTH
John
"PeterH" wrote in message
...
Thanks Partick,
Please forgive the stupid question but I am not familiar with the userform,
textbox or listboxes - how do I set this up?

"Patrick Molloy" wrote:

a userform with a textbox and two listboxe
you can use the textbox as a filter for the listbox

the first listbox, listbox1, holds ALL the data and its visible property is
FALSe, so you can see it.
Load the 2nd listbox from the first.
when a value is typed into the textbox, clear liistbox2 and repopulate from
listbox1, but only items that match the filter

Option Explicit
Private Sub TextBox1_Change()
ListBox2.Clear
Dim index As Long
With ListBox1
For index = 0 To .ListCount - 1
If .List(index) Like "*" & TextBox1.Text & "*" Then
ListBox2.AddItem .List(index)
End If
Next
End With
End Sub
Private Sub UserForm_Initialize()
TextBox1_Change
End Sub

I have a slowing PC, and for 2,500 items, the speed I found accepable

"PeterH" wrote:

Good day all.
Excel 2007 on XP
I am using a data validation list in a cell in order to select a part
number.
However, as the parts list grows (now approx. 2000) it becomes impractical
to run through the dropdown listing box to select.
Anyone aware of an alternative (more user friendly) method to use either
with data validation or using a macro?