Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use VBA to reset data validation (=list) value to first value in that list (list is a named range) | Excel Programming | |||
Crazy Data Validation ... List Validation Not Working | Excel Programming | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Programming |