Excel Search Function

[npomo]

Hi All,

I'm new to advance Excel functions and I'm trying to learn, but so far I've been unable to solve this problem. Essentially, I want to create a personal database full of instruction manuals. The first page of the Excel Worksheet will show the user six radio buttons that pertain to the category of instruction (car, woodwork, painting, etc.) The user chooses one of the radio buttons and proceeds to input a search term, like "chair" and presses the "search command button" to execute the search.

Basically, I need help with the formula/coding for the search button. It must incorporate the search term and the chosen radio button. Does anyone have any idea how to do this? Any help would be much appreciated!

[SamT]

Personally,I would use a VBA UserForm instead of a Worksheet Form.

Planning ahead, I would want to be able to add images to the buttons, for that purpose,and others, I would use CommandButtons instead of OptionButtons.

you can use a TextBox to enter a search term, but a ComboBox both lets the User select from a list, (call it the Top Ten,) or enter a new term.

The Category CommandButtons, cmdCar, cmdWoodWorking, cmdPainting,etc can load the cboSearchList ComboBox with their own custom Top Ten List.

If your Instruction manuals are organized hierarchically, well ComboBoxes can have more than one column, but only show the columns you want.

If cboSearchList becomes cboChapters with Column1 visible and containing a list of chapter titles, and Column2 hidden and containing the list of Chapter locations, cmdSearch becomes cmdGoTo.

And again cboChapters_Change code can load predefined custom lists into cboSections, and cboSections can have it's own GoTo code.

In this schema, the User experience is
Click a Category CommandButton, select a Chapter, click cmdGoTo. Read the Chapter.
OR
Click a Category CommandButton, select a chapter, select a section. Read the section.

These dependent ComboBoxes can be built as deep as you want.

I hope this gives you some ideas of what is possible.

[mdmackillop]

Welcome to VBAX
I agree entirely with Sam. If you can mock up a sample workbook to show proposed data layout, we can assist further with the search and coding. You'll probably find this ends up more complex than originally envisaged. You can add your detailed content later.

[snb]

I can't fully (!) agree with SamT (i.e only 99%).
Especially what he said about Commandbuttons vs. Optionbuttons.

[npomo]

16375

This is what it looks like. The top section is pretty simple and is fully functional. The bottom section is where I'm having trouble. You would input the ingredient, select the radio button, and click search. The search button needs to take into account the word under the Main Ingredient bar as well as the radio button.

[mdmackillop]

It's not clear what you are searching. One column, multiple columns, multiple columns in multiple sheets? What happens with the result(s)?

[SamT]

Pretty, Is that a Screenshot of your main worksheet?

However, If the Main ingredient is "Eggs" and the Sheets name is Breakfast,a Search for "Eggs" on the sheet will return the Cell containing "3 Eggs" in the recipe "SamT's Famous Biscuits and Gravy For an Army" because I put 3 eggs in a 5kilo batch of biscuit mix. It will not return the Cell containing "1 Egg" in the Recipe "Senior Breakfast."

It will also not return a list of all Cells that contain "eggs." For that, you would have to rerun the Search until it found no more "Eggs," plural.

One of our famous members said "Function Follows Structure." That means that you cannot create features that your workbook Structure does not support.

It also means you have to decide on which features you want in your project and design the Workbook Structure to support those Functions.

We have no idea of the Data Structure used in your Excel Workbook.

@ snb, Yeah, I know, but the OP is new to VBA and large Excel projects.

[npomo]

Yes, it's a screenshot of the main worksheet.

This is the structure I was thinking about using now:

I'm going to have a folder on my computer filled with recipes/instructions that have the same format (i.e. column a will have ingredients, column b will have instructions, etc.) One of the cells in each workbook will have be specifically for the "Main Ingredient" -- so if you were to type in "egg" you would only get recipes where that is the main ingredient.

Is there a way, then, to click the search button on the main screen and have it bring you to a new worksheet where it will show you all the recipes that meet you the criteria? I've posted a picture for reference.16378

[snb]

@ snb, Yeah, I know, but the OP is new to VBA and large Excel projects.

So am I :hi:

[SamT]

@ snb
:funnyashe:funnyashe:funnyashe:funnyashe:funnyashe:funnyashe:funnyashe:funn yashe:funnyashe:funnyashe:funnyashe:funnyashe:funnyashe:funnyashe:funnyashe :funnyashe:funnyashe:funnyashe:funnyashe

[SamT]

npomo,

See posts #2, #3, and #4.