Search for a sheet
 

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

Search for a sheet
 

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

Search for a sheet
 

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

Search for a sheet
 

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

Search for a sheet
 

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

Search for a sheet
 

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

Search for a sheet
 

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

Search for a sheet
 

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