• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA coding search box through multiple sheet

roseblack

New Member
Good day everyone,

I am working in an engineering company and have been tasked to create a sort of filter for their excel file. There are hundreds of different sheets of products updated monthly.
Im not sure what vba code to type out, there is a layout of search box on sheet 1 which the user will be able to key any word or number value in text box and similar multiple results from the different sheets of products will populate below it as a full row from month to location. Also im not sure if its possible for photos from data sheets will follow the filter to the sheet 1 too. PS: black collumns also will be populated onto sheet 1 so full row of data...

I kindly seek your guidance anyone out there if you can provide me a sample coding, reference link or you may edit my sheet to test it out thank you very much

regards
 

Attachments

Marc L

Excel Ninja
Hello,​
as this is a wild cross posting according to any Excel forum rules​
you must add a link for each other forum where you have created the same thread …​
 

roseblack

New Member
Hi Marc L,

okay i will sorry.

myonlinetraininghub
ozgrid

If anyone has any solution to my query i seek your guidance thank you and sorry once again for cross posting.

regards
 

Marc L

Excel Ninja
Some link is missing …​
there is a layout of search box on sheet 1
The easy Excel basics beginner way is to use an advanced filter​
with exactly the same layout as the headers (so without any useless text box) like​
row #1 for the same headers and row #2 for the criteria, sample in this thread :​
 

roseblack

New Member
excelforum

sorry yes, thank you marc will check it out.

regards
 
Last edited by a moderator:

Marc L

Excel Ninja
According to your attachment there are 3 columns as criteria :​
is it only for a single criteria or criterion can be mixed ?​
As for an unique criteria another way is a double click worksheet event …​
 

p45cal

Well-Known Member
In the attached:
  • A named range Parameters being Sheet1 cells A1:B3
  • The data on the other sheets have all been made into proper Excel Tables, and the table names all begin with Table. If you have more sheets with data you want to include, just make sure:
    • They are named with a name beginning Table
    • The headers are the same as the existing sheets
  • Any other tables in the workbook that you don't want to include should have a name that does NOT begin Table.
  • The Months parameter in cell B1 has a dropdown; if you want to have all months, just delete the value in the cell.
It would probably be easier if you had the sheets containing only data in a separate workbook. This data workbook can stay closed.
It might be even easier if you could tell us where the data comes from since it could probably be interrogated without it being brought into Excel.
 

Attachments

Marc L

Excel Ninja
According to your need a text box seems useless like you can see with p45cal attachment …​
If you need an unique cell as criteria that means the search is for an unique column like 'Description of Products' ?​
 

roseblack

New Member
@Marc L @p45cal

I want to search either description of products, company name or month. I've tested out on my real data but it does not work...
It shows "object variable or block variable not set" although i have already set the names for all sheets "Table1,2,3,4 so on..." after sheet 1...
total i have 19 sheets that wants to be filtered onto sheet1 also when i want to add new criteria header or edit existing header no data pop up for it..

best regards
rose
 
Last edited:
Top