Expand Search Listbox beyond MatchEntryComplete

[cellist]

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

 

[ker_01]

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

 

[ker_01]

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]

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