How do I perform a partial word search in a cell ?

[Guest]

I have a list of data (just one column).
It has a list of about 800 or more products.
On a seperate worksheet I want to enter products one after the other.
When entering part of it, eg. cil or pen
it should come up with a dropdown box at the side of the cell (like with
data validation dropdown box), with the values that 'contain' or *pen*
so some of the items appearing would be pen, pencil, pendant for example.

I think this would involve a macro that picks up each keystroke as its
entered into the cell (there are about 50 rows that may have this data
entered into it).
it would do a search with every keypress for contains: *pen*
and then use the search values in a data validation dropdown box for that
particular cell. This would be of GREAT help if you can help me out with
this, or even part of it!
Thanks!

 

[Zone]

Rohan, you might like this file:
http://savefile.com/files/152644
After downloading, open the file and display Sheet2. Select an empty
cell in column A. Type in a few letters and select a matching word
from the list. This uses the Worksheet_SelectionChange event for
Sheet2. Hope it helps! James

 

[Guest]

That was really helpful thanks!

I'm still looking to make it a bit less interface and more functionality.
I'm sure this will help.

What I want to do now is to double click on a cell,
have a listbox popup with all prodcuts, and then as i type each letter,
allow that code to work and display the search results in an updating list
box, next to the cell. I'd greatly appreciate any help on this!

 

[Zone]

Rohan, you should be able to replace the Selection Change line with
this line
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
to fire on a double-click. To change this, display the spreadsheet.
Right-click on its tab (bottom of screen) and select View Code.
Replace the SelectionChange line of code with the one above. I'm
buried in work and can't reply more fully at the moment. James

 

[Guest]

Thanks James, for your reply!

I got the double click working for a specified range of cells.
Using Intersect method with Target.

I'm looking to tweak the function a bit, i know what i want but i dont know
how to do it.

Firstly, I want to type the search into the cell itself instead of the text
box in the form, so that it updates the search with each keypress. Also I
would like to try and see how this works:

Populate a listbox with the search results for that cell.

I have a slight prob. with the search. It displays all searches in
alphabetical order. if I have a product called for example "Amazing Pen" and
then another called Pen. when I type Pen, it display 'Amazing Pen' before
'Pen', is there a way to modify it so that if I type 'Pen' It would show Pen
before amazing pen.
or in other words, if I type something then it should display exact matches
first ?

***When it comes to the search, "optimally", I would like the same search
flexibility as the 'J' key in winamp. With that it's possible to type any
portion of the search, in any order and it will display it.***

Any help would be veerrryy much appreciated. The help I've gotten so far has
been fantastic!
Thanks!

 

[Tom Ogilvy]

When typing in a cell, for any practical purpose, macros don't run.

You might look at using the selection change event to pop up a dialog with
the focus set to the control so you can start typing.

 

[Zone]

Rohan, as Tom said, just typing characters into a cell doesn't trigger
an Excel event. The only thing I know that works that way is
auto-complete. I haven't really explored auto-complete, so I don't
know what properties/methods it exposes to VBA, if any. Someone else
may have an idea on that. I think you might be happier with the
routine in its original form, but of course that's up to you. Anyway,