Hi All,
I have seen a post this morning which would suit my new workbook if it could be adapted. "search-and-display-results-to-listbox"
I would like to be able to search through multiple worksheets that contain alot of information, and display searches in a listbox, then when highlighted i can either select the listbox or press a command button to copy the row to a sheet call order rec?
I have attached my workbook with all the sheets, macros and userform for your information.
Can you describe how you would want to use the textboxes? I have something, but I'm not sure how this will relate to the search items.
David
(*) Reputation points appreciated.
I want to use the text boxes as search inputs so if a string of text matches it will pull up the full row in the list box. it can any of the 4 text boxes.
You could just use a combo box, which allows a user to input text and provides suggestions based on preexisting data. This can work as searches. Then make it so it changes the listbox based on afterupdate.
I'm new to this so i understand what you are saying but i have no idea how to create the code.
Are you putting your combobox in a userform? If you are, in the code area put in
The .Range are the cells you wish to provide suggestions for you. If it is on another worksheets, just put Worksheets("Worksheet Name") in front of Range.
Please Login or Register to view this content.
Then for the Listbox, you use If/Then statements.
This is my example:
Please Login or Register to view this content.I am relatively new to this as well. So I am sure if you have many if/then statements, it would get tiring. I am not positive, but there is probably a way to add items in the listbox using the Range feature.Please Login or Register to view this content.
Last edited by Templemind; 11-17-2014 at 04:33 AM.
No, i was just using text boxes and a listbox.
Untitled.jpg
Untitled.png
This is what my userform looks like at the moment. the plan is to input text into any of the 4 boxes and it will search and list the rows in the listbox, then i can add it to an predefined worksheet. "order rec"
Last edited by nathandavies; 11-17-2014 at 04:49 AM.
Sorry for the lack of help and wasting your time, but it is difficult for me to totally understanding your code. When you enter ABB into the description it works out. I just cannot find which worksheet it is referencing and so I am of no realy help. The code that is already there is different than the code I would normally use, and because I am just an amateur I have to say that my skillset is not very broad. I hope you can get further help.
The only thing I would do, and perhaps this would just be my preference, is change the textboxes to comboboxes so that one does not have to remember the exact spelling and would reduce errors.
Thank you for your help, i have changed the form to use combo boxes, i'm now going to change the way I do the application so i use the combo boxes and add it to the sheet from the combo box (if i can!)
Here is what I have so far. See if this is what you're looking for. If it is, I can add the search routine to move selections to the listbox.
the layout is great, i think the combo boxes are a good idea and they should be the worksheet names only, then using the text input box that searches for the part and shows it in the list box.
Ok, that's what I 'm trying to pryout of you. How does the search work? The way it works now, is it searches all the worksheets, except two.
Ok, so, it should be narrowed by worksheet? Then by search phrase? If so, then we don't need four comboboxes/textboxes. We only need one of each.
The search should work based on each header, so if i put a "16A" in description and then "ABB" it will filter all the rows with "16A" from abb in the list box. but if i change it to "20A" it will change the listbox.
maybe on combo box could be used to narrow down the search based on the worksheet name.
The layout could be like this....
Untitled.png
OK, how much information do you need in the listbox?
Desc, Mfg, Part Number, and B&S part number?
DESCRIPTION MANUFACTURER SUPPLIER PART NUMBER B&S Part Number £ EACH
Would it be possible to add a command button so it will copy the full row to the last row of "order rec"
Yes, that is the ultimate goal. But, one step at a time.Originally Posted by nathandavies
This is a complicated macro and I'd rather not go back and rewrite a lot of code when we could possiblely get it right the first time.
As soon as I get it to where it will fill the listbox, I'll upload another copy to make sure you're happy with the results.
Thank you for your continued help!
You are a life saver!
OK, try this out.
In my initial upload, I wasn't worried too much about layout, so this is a good bit different. THere are no textboxes for searches. I set it up to look at each one like a filter using comboboxes.
The move to the sheet still isn't set up yet. My vision will be for the user to move through listbox, selecting the items needed. Then a "Move to sheet" button will move all the SELECTED items to the order sheet.
Yeah i think you have it nailed the layout and everything but i keep getting a runtime error 429.
Also can you add the cost to the list box please?
Then i get a runtime 9 when i try to put text in the search phrase combo box as well
HA! You'll have to tell me how you did that. I tested it for about 30 minutes, but never encountered that one.runtime error 429
Hmmm..., I thought I did. I noticed that some on the headers are not named the same. You'll have to standardize the column header name as I use it to determine which column to work in.can you add the cost to the list box
Last edited by Tinbendr; 11-17-2014 at 04:48 PM.
I'm running a MAC at home not a PC so not sure if that would make a difference?
Yeah i will run through all the headers and make them all the same and in upper case. haha!
The dictionary object will not work with Mac.
ill put it on my other laptop and test it
I will have to test it at work tomorrow, but from what I have seen it looks like you have got all the information in there that i require!
I have test the workbook this morning and its working great, not getting any run time errors, the only thing is the line up of the cells and titles in the list box but i will sort that out once it is finished!
after playing with the form some more this afternoon i have noticed that you can not search all worksheets, would it be possible to add an extra option to search all?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks