Data Valadation to find worksheets?

[smithomatic]

Hello!

I was wandering if anyone can help me.

I am in need of a way to search for a worksheet in an excel workbook when the "sheets tab" at the bottom is hidden (by me, i dont want anyone else to move sheets around).

I was thinking there might be a way to do this with data valadation, perhaps using a list and a cell next to the list with =hyperlink function.

Or possibly some kind of Lookup macrod to a button so I can perform the lookup and pull the page to the front.

Any other ways please do say! all help much appreciated.

(i dont want to have to right click the arrows on page one to choose a worksheet to go to as this toolbar will be hidden.)



Many thanks!

 

[VoG]

A UDF in a standard module

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

To use

=worksheetexists("Sheet12")

 

[smithomatic]

Thank you for your rapid response! Unfortunatly, although I have basic skills on Excel im not an expert when it comes to code, would it be possible for you to post an example file for me to disect? I think this will better help me learn how youir suggestion works.


Many thanks

Josh!

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste the code into the white space on the right. Press ALT + Q to close the window.

On your sheet you can then use for example

=worksheetexists("Sheet3")

 

[smithomatic]

Hi Peter,

All im getting is a cell that responds "false" no matter where I use your code? How do I use this to find a worksheet?

many thanks

Josh

 

[VoG]

I have uploaded an example file

http://www.box.net/shared/pj4thtqd9o

 

[smithomatic]

Thanks very much Peter, I have got it now!