search box questions

i have a command button that brings up a search box and i enter a name and it searches all my sheets one at a time. and i get a message for each result on each worksheet the name is found on that i have to close individually). I would like to have it instead find all the records and display them in a window and then be able to select the record I want from the sheet i want, and have it go to that sheet and record. So the results box should show possible results from all pages but should give a preview of that result.

so if i type in "Smith" it should show something like

Last Name	First Name	Auto Ship	AS date	Stree Add	City	State	Sheet Name
Smith	John	3YA	7/15/2015	1223 my street	My town	NY	Sheet 1
Smith	Bill			555 your street	Paris	CA	Sheet 2

Then be able to click on one of the results and go to that page.

Here is what i have for code now.

Please Login or Register  to view this content.

You are going to need to create a UserForm for what you want to do. Submit your workbook.

ok how do i add the user form and such.
Here is a sample of my workbook.
test.xlsm

Can anyone assist?

OK let me test a bit and ill let you know. Thanks for all your help.

is there a way to make the results come up in a user form rather than a sheet?

Please don't get me wrong, i appreciate it. and it works great and is very usable. I was just hoping to have it come up in a separate window (or user from) like the search box does. To me, it is more visually appealing so that it's not switching pages during the search in case i want to search for something else.

Also can i have the results sort by name (Column A then Column B) rather than by sheet?

ok so I am having trouble converting your code to a listbox and was hoping you could help me.

the listbox (userform) code is from another workbook i use. I am trying to convert but i am too confused.

first here is your code:

Please Login or Register  to view this content.

then here is the code from from the listbox(userform):

Please Login or Register  to view this content.

please help me out.