Find a sheet with code

[dar149]

Does anyone know if you can write code to find a specific sheet tab? I'd like to show a prompt where the user can type in a worksheet name and then have the code activate the sheet they typed in. I can do this to find text on a sheet, but not to find a sheet tab within a workbook.

Thanks...

 

[SkipVought]

Hi,

Assuming that the sheet name is in SheetName

Worksheets(SheetName).Activate

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[acron]

Something like :

Sub FindSheet()
    On Error GoTo ErrHandler
    Sheets(InputBox("Enter Name of Sheet to Activate :")).Activate
    Exit Sub
ErrHandler:
        MsgBox "Sheet not found"
End Sub

A.C.

 

[TonyJollans]

Hi dar149,

Here's one way ...

  Dim Prompt As String
  
  Prompt = "Enter Sheet Name"
  GoTo GetSheet

InvalidSheet:
  Prompt = InputNAme & " is not a valid Sheet Name.  Please Re-Enter"
  Resume GetSheet

GetSheet:
  InputNAme = InputBox(Prompt)
  On Error GoTo InvalidSheet
  Sheets(InputNAme).Activate

Enjoy,
Tony

 

[Luis939]

In TonyJollans' code

what is the exact difference between Resume and Goto, why use one or the other? Thanks

 

[TonyJollans]

Hi Luis939,

Goto just goes to a line (or a label).

Resume only has meaning after an error has been trapped and it ends the error handling and then does a Goto. It is commonly used in statements like On Error Resume Next

In the code here, if there is an error when trying to Activate the sheet (possibly any error but most likely 'sheet does not exist') then error handling is activated as a result of the On Error statement and control GoesTo the InvalidSheet label. Here the text of the prompt is changed and then the error handler is de-activated by the Resume which then GoesTo the GetSheet label. If the error handling was not de-activated, it would not be possible to (re-)activate it before trying again.

Enjoy,
Tony.

 

[dar149]

Thanks to everyone for your quick responses. I always learn a lot from everyone's input.