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

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.
--
Regards,
Tom Ogilvy

"Chance224" wrote in message
...
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

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" wrote:

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.
--
Regards,
Tom Ogilvy

"Chance224" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy


"Chance224" wrote in message
...
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" wrote:

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.
--
Regards,
Tom Ogilvy

"Chance224" wrote in message
...
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

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


--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Chance224" wrote in message
...
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" wrote:

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.
--
Regards,
Tom Ogilvy

"Chance224" wrote in message
...
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

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

"Chance224" wrote:

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

See my answer to our first post.

--
Regards,
Tom Ogilvy

"Chance224" wrote in message
...
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

"Chance224" wrote:

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

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.

"Chance224" wrote:

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