Search for worksheet by name or cell value

[Mayweed]

I'm using the code below to search for and display any sheet in a workbook, this works fine but I would like to be able to use the same input box to search on a value in cell A1.
So if the user enters the sheet name OR the value of cell A1 the appropriate sheet will be displayed.
I've attached the workbook in the hope that someone can help me out with this.

Please Login or Register  to view this content.

[snb]

Why don't you use Excel's inbuilt facility:

[Leith Ross]

Hello mayweed,

Add this macro to your workbook.

Worksheet Change Event Macro

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro

1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

[Mayweed]

Thanks Leith but I cant get that macro to work at all, must something I'm doing wrong?

Just to be clear... the macro should open an input box so that the user can enter a sheet name OR a string.

If the string matches the value of any A1 cell of any sheet in the workbook, that sheet is displayed.
OR
If the string matches the name (on the tab) of any sheet in the workbook, that sheet is displayed.

[Leith Ross]

Hello Mayweed,

Thanks for the updated info. I now understand what you want and that explains why the macro doesn't work. The macro needs to be rewritten.

[Leith Ross]

Hello Mayweed,

I think this macro will do what you want. Try it and let me know.

Please Login or Register  to view this content.

[Mayweed]

Thanks Lieth, I tried your code and it selects the sheet by name but not by A1 cell value.
I've attached a workbook to demonstrate.
If the user enters the word "Blue", sheet 2 should display
If the user enters "1234", sheet 1 should display

[Leith Ross]

Hello Mayweed,

You have no sheets named "1234", "Blue". "Orange", etc. The sheet names are "Sheet1", "Sheet2", "Sheet3".

[Mayweed]

My original post/attachment had code that searched and displayed the sheets by tab name, I would like to also be able to enter a string into the input box as follows.

Locate and display a sheet based on users 'input box' value

Method 1. Locate a sheet by entering the sheet name (This is working ok.)

Method 2. Locate a sheet by entering a string (eg. "Orange" or "1234")
Loop through all sheets in the workbook looking in cell A1 for the string (in this case "Orange")

[Leith Ross]

Hello Mayweed,

This ignores the tab names and uses only the input name or values in "A1".

Please Login or Register  to view this content.

[Mayweed]

Brilliant! it works just fine but would you combine the two so that I can enter the sheet name OR the string into the same input box?

[Mayweed]

Oops! You've already done that.
Thanks for your help Leith, your code works perfectly.