Search for a sheet

[Guest]

I have a work book for payroll that has a sheet for each employee. Is there a
way to write a macro that will search and select a sheet. If possible I
would like to be able to que the macro with a short cut such as Ctrl+s. I
know you can select a sheet and do Ctrl+F, but I have a summary page that is
linked to each employee sheet and it seraches through that sheet first.

Thanks,
Chance

 

[Tom Ogilvy]

right click on the lower left corner where you see the VCR controls. This
will give you a list of sheets to choose from.

Otherwise

Sub SelectSheet()
res = InputBox("Enter employee (sheet) name")
on Error resume next
set sh = worksheets(res)
On Error goto 0
if sh is nothing then
msgbox res & " is not a valid sheet name"
else
sh.activate
End if
End Sub

paste into a general module. go back to Excel. Go to Tools=>Macro=>Macros,
select the SelectSheet macro and click on options. This will allow you to
assign a shortcut key.

 

[Guest]

I can use the following formula to activate a sheet. Is there a way to link
it to a msg box instead of a cell?
Sub GoToIt()
Dim s As String
s = Range("A1") 'sheet name To search In
Sheets(s).Activate

End Sub

Thanks,
Chance

 

[Guest]

There is already a facility to present the list of sheets in a menu. On the
bottom left of your Excel screen you'll find arrow heads for navigations. If
you right click in this area a menu pops up.

 

[Tom Ogilvy]

See my answer to our first post.

 

[Guest]

The macro works great! The only thing is when you type in an invalid entry it
goes to Debug.

On Error GoTo 0
If sh Is Nothing Then
MsgBox res & " is not a valid sheet name"
Else
sh.Activate
End If
End Sub

Thanks,
Chance

 

[Tom Ogilvy]

In the vbe, go to Tools=>Options, general tab and make sure you have Break
on Unhandled errors selected instead of Break on all errors.

 

[Tom Ogilvy]

Disregards,
My omission

this should do it:

Sub SelectSheet()
Dim sh As Worksheet
res = InputBox("Enter employee (sheet) name")
On Error Resume Next
Set sh = Worksheets(res)
On Error GoTo 0
If sh Is Nothing Then
MsgBox res & " isn not a valid sheet name"
Else
sh.Activate
End If
End Sub