Simple "SEARCH ENGINE" in a userform, from data across multiple sheets

**daisiroi** · 01-14-2016, 04:43 PM

TEST0011.beta.xlsm

Good Day Everyone,

I'm basically new to the fascinating world of VBA and I am very much willing to learn more about it. I've read a few forums about it as well in the hopes of being able to create a few simple userforms that will make my current job easier. I have a simple database which I want to turn into a simple "search engine", as you may call it. I already made a userform complete with the buttons I wanted it to have. What I'm stuck with, however, is how to have the Button perform a search function from sheet 2 based on whatever word they type on the textbox.
Simply put, here's a list of how I want to use my Userform:

1. Choose a category by ticking on the options "police department" and "states"
2. Select a State from the combobox
3. Type a part of the word (specifically a county) in the textbox.
(The textbox will auto populate a few other counties in reference to the selected state in the combobox, narrowing the search options)
4. On click of the Search button, Userform2 will show the results in a listbox. (Data to show here will be referenced from the workbook's sheet 2)

*As of now, I only have 2 columns of data in sheet 2, but I will need to eventually add a few more columns on that to show other data related to the items on column A but I wanted to have the userform working first so I can just add it once I collect all the data I need.

I would really appreciate anyone who can help me out on this. I tried to do this without a userform and just using simple vlookup formulas but my main objective is to not have the database (workbook) visible when using it. I wanted it as user-friendly as possible as this will be used by my team members and colleagues as deemed necessary.

I attached a test workbook for everyone to check out.

Hope anyone can help.
Thank you!

**laansesu** · 01-29-2016, 03:06 AM

I'm not aware of VB but just have a data validation custom formula that searches from the data. let say your data in F column & row starts from 14 to 48 then formula would be

=OFFSET($F$14:$F$48,,,COUNTIF($F$14:$F$48,"?*"))

**LordLoki** · 01-29-2016, 04:50 AM

Hi Daisiroi,

Can you upload the sheet that you have already with your userform?.
Just write some madeup Data in the Columns if its sensitive data.
That would make it a lot easier to help you

Greets
Loki

Sorry just overlooked the Link :D

**daisiroi** · 01-29-2016, 02:33 PM

Hi, Thank you for the suggestion, I was going for VBA as I will need something that will hide the data from which the search will be conducted, so eventually I'll be making it so that only the User form from which they will do the search on, will be shown. ( the workbook itself will be hidden once they open the file.) I've tried other threads too, but since we have limited internet access in the office I was hoping I can pull this through using you guys' help in here. :D

**daisiroi** · 01-29-2016, 02:40 PM

Originally Posted by LordLoki

Sorry just overlooked the Link :D

Hi, Thank you for taking interest in answering my question, I was starting to feel no one's gonna be helping me, but I really need this thing to work. My team needs it badly. The data in the uploaded workbook is as it is, although not yet complete. As you can see I only have one state input, eventually I will be needing to have other states uploaded. all 50 states of them. I just needed to have it working before I can add additional data. I realized it will be easier to work with, but let me know if you need the complete data. I will really appreciate any help given.

**LordLoki** · 02-02-2016, 07:53 AM

hi Daisiroi,

find attached an example that should do the trick for you.

just one thing you should make sure that the Data in the List Sheet Exactly Matches the Data that you use in the counties and department sheets. There are some spaces and special chars that should not be there. i also made the search itself with Ucase so that it does not check case sensitive