looking for macro to search workbook

Stevesmail

New Member
Joined
May 3, 2021
Messages
10
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello

I thought I would ask you good people a question on a workbook I'm developing for my place of employment. We have many SDS sheets for the many chemicals we have at the workplace. The company didn't have very good organization with the sheets which can be a safety and fire hazard because of not finding SDS in good time.

I offered to organize these and so I'm developing an excel workbook. In the workbook, I'm going to have several tabs. These SDS sheets will be organized based on manufacturer of the chemicals. I have column A with the mfg name, 2nd column has chemical name and the last column will have a hyperlink to the SDS file on the company server. The tabs will be set up as A, B, C, etc

What I've been looking for is a Macro that can do a search in the whole workbook for the manufacture name or chemical name which is in the first 2 columns. First tab will the search page where I'll put a search button and clear button on the page. What I'm wanting is for a person to either look for the SDS by clicking on the tab corresponding to the manufacturer and scroll through the manufacturers to find what they are looking for or go to the search page and type in the manufacture name or chemical name and either go to the cell automatically from the search in the workbook or bring a copy of the searched line onto the search page which will be faster to access the SDS. I haven't found a macro to do what I need it to do yet and thought someone here could guide me in the right direction. Thanks
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
Since I didn't have a picture of the Search worksheet, I simply created one with two Listboxes (Listbox1 and Listbox2) and two Textboxes (Textbox1 and Textbox2). Use Textbox1 to select the Manufacturer, use Textbox2 to select the chemical. For example, if you want to find Manufacturer "BASF", click into Textbox1. Listbox1 should populate with all the Manufacturers. Start typing "BASF". As you type the Listbox1 will respond with an increasingly smaller list of names. When you see "BASF" in Listbox1 you can click on it and it will take you to that entry on the proper worksheet. Note that you don't need to type out "BASF" totally to find the entry. The same would apply to the chemicals using Textbox2 and Listbox2. A nice feature is particularly useful for the chemicals: suppose you want to find a chemical whose name contains "dimethyl" somewhere in it. Click in Textbox2, enter "*dimethyl" (without the quote marks), you should get a list with all chemicals which contain that string. Inotherwords, the * works as a wildcard in the search. More to talk about but will wait till you have seen it and have questions.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
if you want to upload the workbook you can use an online site like Dropbox and post a link to it or if you don't have that facility you can send in email to:
vw412 at vwandsw dot net
 

Forum statistics

Threads
1,147,559
Messages
5,741,808
Members
423,689
Latest member
Jords998

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top