I am using the following two macros to return the user to the last active
sheet after visiting another sheet (when they leave the first sheet the first
macro runs; when wanting to return, they run the second). The problem is that
it forces them to go momentarily to the sheet "Summary" first. Is there a
better way to do this? I searched all the other strings and could not find
something that would work. Hiding the "Summary" sheet did not work either.
Thanks in adavance,
Sub FreezePreviousWorksheet()
Previous = ActiveSheet.Name
Application.Goto Sheets("Summary").Range("A1")
ActiveCell = Previous
End Sub
Sub ReturnToPreviousWorksheet()
Previous = Sheets("Summary").Range("A1")
Sheets(Previous).Select
End Sub

Try this


Sub ReturnToPreviousWorksheet()
Sheets(ThisWorkbook.Previous).Select
End Sub


and


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Previous = Sh.Name
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Carl" wrote in message
...
I am using the following two macros to return the user to the last active
sheet after visiting another sheet (when they leave the first sheet the
first
macro runs; when wanting to return, they run the second). The problem is
that
it forces them to go momentarily to the sheet "Summary" first. Is there a
better way to do this? I searched all the other strings and could not find
something that would work. Hiding the "Summary" sheet did not work either.
Thanks in adavance,
Sub FreezePreviousWorksheet()
Previous = ActiveSheet.Name
Application.Goto Sheets("Summary").Range("A1")
ActiveCell = Previous
End Sub
Sub ReturnToPreviousWorksheet()
Previous = Sheets("Summary").Range("A1")
Sheets(Previous).Select
End Sub

hi
bob is far more experienced than me but to try and answer your delema....
this line is what is sending you to summary.....
Application.Goto Sheets("Summary").Range("A1")
i.e. goto summary...your are sending it to summary.
what i did is eliminate this line and replaced it with this....
Sub FreezePreviousWorksheet()
Previous = ActiveSheet.Name
Sheets("Summary").Range("A1").Value = Previous
End Sub

Sub ReturnToPreviousWorksheet()
Previous = Sheets("Summary").Range("A1")
Sheets(Previous).Select
End Sub

worked in xp. not sure about 07. i haven't worked with that yet.

regards
FSt1

"Carl" wrote:

I am using the following two macros to return the user to the last active
sheet after visiting another sheet (when they leave the first sheet the first
macro runs; when wanting to return, they run the second). The problem is that
it forces them to go momentarily to the sheet "Summary" first. Is there a
better way to do this? I searched all the other strings and could not find
something that would work. Hiding the "Summary" sheet did not work either.
Thanks in adavance,
Sub FreezePreviousWorksheet()
Previous = ActiveSheet.Name
Application.Goto Sheets("Summary").Range("A1")
ActiveCell = Previous
End Sub
Sub ReturnToPreviousWorksheet()
Previous = Sheets("Summary").Range("A1")
Sheets(Previous).Select
End Sub

Thanks, Bob, but that did not do exactly what I needed (in spite of your
excellent instructions). Thanks again.

"Bob Phillips" wrote:

Try this


Sub ReturnToPreviousWorksheet()
Sheets(ThisWorkbook.Previous).Select
End Sub


and


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Previous = Sh.Name
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Carl" wrote in message
...
I am using the following two macros to return the user to the last active
sheet after visiting another sheet (when they leave the first sheet the
first
macro runs; when wanting to return, they run the second). The problem is
that
it forces them to go momentarily to the sheet "Summary" first. Is there a
better way to do this? I searched all the other strings and could not find
something that would work. Hiding the "Summary" sheet did not work either.
Thanks in adavance,
Sub FreezePreviousWorksheet()
Previous = ActiveSheet.Name
Application.Goto Sheets("Summary").Range("A1")
ActiveCell = Previous
End Sub
Sub ReturnToPreviousWorksheet()
Previous = Sheets("Summary").Range("A1")
Sheets(Previous).Select
End Sub

Perfect! Thanks. I knew why it was going to the Summary sheet but did not
know how to get it to enter the name of the last active sheet in cell A1 of
the Summary sheet without going there. Thanks again.

"FSt1" wrote:

hi
bob is far more experienced than me but to try and answer your delema....
this line is what is sending you to summary.....
Application.Goto Sheets("Summary").Range("A1")
i.e. goto summary...your are sending it to summary.
what i did is eliminate this line and replaced it with this....
Sub FreezePreviousWorksheet()
Previous = ActiveSheet.Name
Sheets("Summary").Range("A1").Value = Previous
End Sub

Sub ReturnToPreviousWorksheet()
Previous = Sheets("Summary").Range("A1")
Sheets(Previous).Select
End Sub

worked in xp. not sure about 07. i haven't worked with that yet.

regards
FSt1

"Carl" wrote:

I am using the following two macros to return the user to the last active
sheet after visiting another sheet (when they leave the first sheet the first
macro runs; when wanting to return, they run the second). The problem is that
it forces them to go momentarily to the sheet "Summary" first. Is there a
better way to do this? I searched all the other strings and could not find
something that would work. Hiding the "Summary" sheet did not work either.
Thanks in adavance,
Sub FreezePreviousWorksheet()
Previous = ActiveSheet.Name
Application.Goto Sheets("Summary").Range("A1")
ActiveCell = Previous
End Sub
Sub ReturnToPreviousWorksheet()
Previous = Sheets("Summary").Range("A1")
Sheets(Previous).Select
End Sub