Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expand Search Listbox beyond MatchEntryComplete
My application has a listbox whose data contains entries like to the following:
Groceries - Food Groceries - Paper Items Groceries - Health and Hygiene Office Supplies - Paper Items Party Supplies - Paper Items Dinnerware - Paper If the user is not familiar with the structure of these categories, they might try browsing the list for "food" or "pape" or "heal", instead of going for "groc", and think that there is no category defined for the particular item they are interested in. Upon not finding any items that start with these character strings, the user would be faced with browsing the entire list. I'd like to provide the ability to search the list on specific words or strings other than the first characters of the entry. For example, the user might want to look for "paper" or "paper items" and get back a list showing: Groceries - Paper Items Office Supplies - Paper Items Party Supplies - Paper Items Dinnerware - Paper etc. By clicking one of the items in this subset of the listbox items, the process would then behave as if the user had clicked on the item from the listbox. Could someone suggest an approach to implementing such a capability? I want the user NOT to have to things that require excel-specific knowledge such as "click find, then copy the full text of the entry, then go back to the user form and paste it into the listbox...". In other words, I'd like the user only to have to click command button on the user form and be prompted for the seach parameter and then be returned to the user form with a famliar "choose one of the entries" type of control such as another listbox. TIA, Phil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expand Search Listbox beyond MatchEntryComplete
Two possible approaches come to mind, neither of which are perfect;
Go to Chip Pearson's site and read the following: http://www.cpearson.com/excel/noblanks.htm Option 1: Based on this, you could have your original list, but create a shadow range using a cell in which your user can type their key phrase (let's say cell B1). Us a formula something like: =IF(ISERR(FIND($B$2,A2)),"",A2) and drag it down. Now you have a list that shows values that include the value in B1, and the rest are blank. Now use Chip's approach to sort them all up into a shorter area. Let's say you do that in cells C1:C? (however large your range is). Now, if the user types anything in B1, they can see all the matches in column C. The list is independent of the drop-down validation list, but at least it gives them some ideas of what to look for based on their search term. Option2: Or, you can extend that idea and just link your dropdown validation to the NoBlanksRange that you created per Chip's instructions. The downside is that the user has to clear B1 each time they want to create a new list, and (I didn't test this) they probably are forced to enter /something/ in B1 to get any list at all. To address these shortfalls, you might have to manipulate your named range to point to NoBlanksRange if B1 has anything in it, or the original range if B1 is blank (simple IF statement in the named range). It would also be a good idea to do a CountA on the range to see how many values are actually returned, and limit the size of your named range to that, so you don't have a lot of blank values at the end of your dropdown. HTH, Keith "cellist" wrote: My application has a listbox whose data contains entries like to the following: Groceries - Food Groceries - Paper Items Groceries - Health and Hygiene Office Supplies - Paper Items Party Supplies - Paper Items Dinnerware - Paper If the user is not familiar with the structure of these categories, they might try browsing the list for "food" or "pape" or "heal", instead of going for "groc", and think that there is no category defined for the particular item they are interested in. Upon not finding any items that start with these character strings, the user would be faced with browsing the entire list. I'd like to provide the ability to search the list on specific words or strings other than the first characters of the entry. For example, the user might want to look for "paper" or "paper items" and get back a list showing: Groceries - Paper Items Office Supplies - Paper Items Party Supplies - Paper Items Dinnerware - Paper etc. By clicking one of the items in this subset of the listbox items, the process would then behave as if the user had clicked on the item from the listbox. Could someone suggest an approach to implementing such a capability? I want the user NOT to have to things that require excel-specific knowledge such as "click find, then copy the full text of the entry, then go back to the user form and paste it into the listbox...". In other words, I'd like the user only to have to click command button on the user form and be prompted for the seach parameter and then be returned to the user form with a famliar "choose one of the entries" type of control such as another listbox. TIA, Phil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expand Search Listbox beyond MatchEntryComplete
On second thought, CountA might not work (end of my post). A better solution
might be: SUMPRODUCT((LEN(NoBlanksRange)0)*1) "ker_01" wrote: Two possible approaches come to mind, neither of which are perfect; Go to Chip Pearson's site and read the following: http://www.cpearson.com/excel/noblanks.htm Option 1: Based on this, you could have your original list, but create a shadow range using a cell in which your user can type their key phrase (let's say cell B1). Us a formula something like: =IF(ISERR(FIND($B$2,A2)),"",A2) and drag it down. Now you have a list that shows values that include the value in B1, and the rest are blank. Now use Chip's approach to sort them all up into a shorter area. Let's say you do that in cells C1:C? (however large your range is). Now, if the user types anything in B1, they can see all the matches in column C. The list is independent of the drop-down validation list, but at least it gives them some ideas of what to look for based on their search term. Option2: Or, you can extend that idea and just link your dropdown validation to the NoBlanksRange that you created per Chip's instructions. The downside is that the user has to clear B1 each time they want to create a new list, and (I didn't test this) they probably are forced to enter /something/ in B1 to get any list at all. To address these shortfalls, you might have to manipulate your named range to point to NoBlanksRange if B1 has anything in it, or the original range if B1 is blank (simple IF statement in the named range). It would also be a good idea to do a CountA on the range to see how many values are actually returned, and limit the size of your named range to that, so you don't have a lot of blank values at the end of your dropdown. HTH, Keith "cellist" wrote: My application has a listbox whose data contains entries like to the following: Groceries - Food Groceries - Paper Items Groceries - Health and Hygiene Office Supplies - Paper Items Party Supplies - Paper Items Dinnerware - Paper If the user is not familiar with the structure of these categories, they might try browsing the list for "food" or "pape" or "heal", instead of going for "groc", and think that there is no category defined for the particular item they are interested in. Upon not finding any items that start with these character strings, the user would be faced with browsing the entire list. I'd like to provide the ability to search the list on specific words or strings other than the first characters of the entry. For example, the user might want to look for "paper" or "paper items" and get back a list showing: Groceries - Paper Items Office Supplies - Paper Items Party Supplies - Paper Items Dinnerware - Paper etc. By clicking one of the items in this subset of the listbox items, the process would then behave as if the user had clicked on the item from the listbox. Could someone suggest an approach to implementing such a capability? I want the user NOT to have to things that require excel-specific knowledge such as "click find, then copy the full text of the entry, then go back to the user form and paste it into the listbox...". In other words, I'd like the user only to have to click command button on the user form and be prompted for the seach parameter and then be returned to the user form with a famliar "choose one of the entries" type of control such as another listbox. TIA, Phil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expand Search Listbox beyond MatchEntryComplete
Keith,
Thanks for your reply. I like the idea of creating what you call a shadow list. I'm going to try putting an option on the already-exisiting form that will allow the user to navigate to a new form whose purpose will be to accept the user's search criteria, do the search on the listbox data, and create a separate listbox containing only the entries that match the search criteria. The user will then select the desired entry and the code behind the form will pass the choice along to the main form's code to be handled as if the user had selected it from the main form. If the user doesn't find an entry that fits their search critera, they can simply be returned to the main form or be given the opportunity to try other criteria. The Pearson code that you cite will provide useful examples for the code I will need to write. Thanks, Phil "ker_01" wrote: Two possible approaches come to mind, neither of which are perfect; Go to Chip Pearson's site and read the following: http://www.cpearson.com/excel/noblanks.htm Option 1: Based on this, you could have your original list, but create a shadow range using a cell in which your user can type their key phrase (let's say cell B1). Us a formula something like: =IF(ISERR(FIND($B$2,A2)),"",A2) and drag it down. Now you have a list that shows values that include the value in B1, and the rest are blank. Now use Chip's approach to sort them all up into a shorter area. Let's say you do that in cells C1:C? (however large your range is). Now, if the user types anything in B1, they can see all the matches in column C. The list is independent of the drop-down validation list, but at least it gives them some ideas of what to look for based on their search term. Option2: Or, you can extend that idea and just link your dropdown validation to the NoBlanksRange that you created per Chip's instructions. The downside is that the user has to clear B1 each time they want to create a new list, and (I didn't test this) they probably are forced to enter /something/ in B1 to get any list at all. To address these shortfalls, you might have to manipulate your named range to point to NoBlanksRange if B1 has anything in it, or the original range if B1 is blank (simple IF statement in the named range). It would also be a good idea to do a CountA on the range to see how many values are actually returned, and limit the size of your named range to that, so you don't have a lot of blank values at the end of your dropdown. HTH, Keith "cellist" wrote: My application has a listbox whose data contains entries like to the following: Groceries - Food Groceries - Paper Items Groceries - Health and Hygiene Office Supplies - Paper Items Party Supplies - Paper Items Dinnerware - Paper If the user is not familiar with the structure of these categories, they might try browsing the list for "food" or "pape" or "heal", instead of going for "groc", and think that there is no category defined for the particular item they are interested in. Upon not finding any items that start with these character strings, the user would be faced with browsing the entire list. I'd like to provide the ability to search the list on specific words or strings other than the first characters of the entry. For example, the user might want to look for "paper" or "paper items" and get back a list showing: Groceries - Paper Items Office Supplies - Paper Items Party Supplies - Paper Items Dinnerware - Paper etc. By clicking one of the items in this subset of the listbox items, the process would then behave as if the user had clicked on the item from the listbox. Could someone suggest an approach to implementing such a capability? I want the user NOT to have to things that require excel-specific knowledge such as "click find, then copy the full text of the entry, then go back to the user form and paste it into the listbox...". In other words, I'd like the user only to have to click command button on the user form and be prompted for the seach parameter and then be returned to the user form with a famliar "choose one of the entries" type of control such as another listbox. TIA, Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Listbox Values | Excel Programming | |||
search and expand featu programming | Excel Programming | |||
How do I expand Excel lookup function to search more than 16,384 r | Excel Programming | |||
Listbox value return from search | Excel Programming | |||
Listbox "expand range as list grows" | Excel Programming |