Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I perform a partial word search in a cell ?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I perform a partial word search in a cell ?
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 RohanP7 wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I perform a partial word search in a cell ?
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I perform a partial word search in a cell ?
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 RohanP7 wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I perform a partial word search in a cell ?
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I perform a partial word search in a cell ?
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. -- Regards, Tom Ogilvy "RohanP7" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I perform a partial word search in a cell ?
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, good luck and best regards, James RohanP7 wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Perform search for a one letter match. | Excel Discussion (Misc queries) | |||
why is there no excel11.xlb file when I perform a search? | Excel Discussion (Misc queries) | |||
Partial string search w/o VBA? | Excel Discussion (Misc queries) | |||
partial search | Excel Programming | |||
how to use partial (word) search | Excel Programming |