[SOLVED] Return to previous worksheet

[Mats Samson]

Hi,
I have two workbooks (WTNSystem and WTNDatabase) open, with several sheets
in each workbook.
I’d like to create a Return-buttons in the sheets that takes me back to the
previous sheet, even if it was in the other workbook. The originating sheet
can be different each time too.
I know that Public variables aren’t particularly reliable but maybe there is
a solution?! Furthermore, I’d like to keep this code in the hidden
Personal.xls, so the code can be used by any sheet calling it.
Thanks
Mats

[Tom Ogilvy]

You would need to instantiate Application Level events and then keep track
of what sheets/cells were selected. Rather than put bottons on sheets, you
could use a floating toolbar.

See Chip Pearson's page for basic information on Application.Level events

http://www.cpearson.com/excel/appevent.htm

--
Regards,
Tom Ogilvy


"Mats Samson" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I have two workbooks (WTNSystem and WTNDatabase) open, with several sheets
> in each workbook.
> I'd like to create a Return-buttons in the sheets that takes me back to
the
> previous sheet, even if it was in the other workbook. The originating
sheet
> can be different each time too.
> I know that Public variables aren't particularly reliable but maybe there
is
> a solution?! Furthermore, I'd like to keep this code in the hidden
> Personal.xls, so the code can be used by any sheet calling it.
> Thanks
> Mats
>

[sebastienm]

Hi,
You can use a small class to track the sheet Deactivate event of the
Application object. In the Personal book:
- Add a class module and call it ClsSheetTracker. Paste the following code
ion this class module
' ------------------------------------------------------
Option Explicit

Private WithEvents App As Application
Private mLastSheet As Object

'------------------------------------
Public Property Get LastSheet() As Object
Dim s As String
If mLastSheet Is Nothing Then
Set LastSheet = Nothing
Else
On Error Resume Next 'capture automation error if book has been
closed
s = mLastSheet.Name
If Err <> 0 Then
Set mLastSheet = Nothing
Set LastSheet = Nothing
Else
Set LastSheet = mLastSheet
End If
End If
End Property

Public Sub GotoLastSheet()
If Not LastSheet Is Nothing Then
On Error Resume Next 'if hidden book or other error
LastSheet.Parent.Activate
LastSheet.Activate
End If
End Sub
'------------------------------------

Private Sub App_SheetDeactivate(ByVal Sh As Object)
Set mLastSheet = Sh
End Sub

Private Sub Class_Initialize()
Set App = Application
End Sub

Private Sub Class_Terminate()
Set App = Nothing
End Sub
'-----------------------------------------------------

As you can see in the above code, when a sheet is Deactivated the mLastSheet
variable is set, keeping track of that deactivated sheet.
Also there is 2 public methods, one that returns the Last Sheet, the other
one is a sub (GotoLastSheet) that activates the last sheet.

- Now in a regular module, create a public variable of the above class and
add a Sub that calls the GotoLastSheet of the above class.

'----------------------------------------------------
Option Explicit
Public Tracker As ClsSheetTracker

Sub GotoLast()
Tracker.GotoLastSheet
End Sub
'----------------------------------------------------

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Mats Samson" wrote:

> Hi,
> I have two workbooks (WTNSystem and WTNDatabase) open, with several sheets
> in each workbook.
> I’d like to create a Return-buttons in the sheets that takes me back to the
> previous sheet, even if it was in the other workbook. The originating sheet
> can be different each time too.
> I know that Public variables aren’t particularly reliable but maybe there is
> a solution?! Furthermore, I’d like to keep this code in the hidden
> Personal.xls, so the code can be used by any sheet calling it.
> Thanks
> Mats
>

[Mats Samson]

Thank you guys for all efforts!
But meanwhile I managed to solve the matter rather simple.
It’s maybe not as fancy as Sebastien’s and you might think I cheat a bit
but it became quite straightforward.
I put all the following code in Personal.xls, including 2 named cells, WB
and WS.

Sub SetPrevious()
Workbooks("Personal").Worksheets("Data").Range("WB") = ActiveWorkbook.Name
Workbooks("Personal").Worksheets("Data").Range("WS") = ActiveSheet.Name
End Sub

Sub ReturnToPrevious()
WBGoTo = Workbooks("Personal").Worksheets("Data").Range("WB")
WSGoTo = Workbooks("Personal").Worksheets("Data").Range("WS")
SetPrevious
If ActiveWorkbook.Name = "WTNDatabase.xls" Then
ActiveWindow.WindowState = xlMinimized
Else
ActiveWindow.WindowState = xlNormal
End If
Workbooks(WBGoTo).Worksheets(WSGoTo).Activate
End Sub

Sub GoToWSSY()
SetPrevious
Workbooks("WTNSystem").Worksheets("System").Activate
End Sub
Sub GoToWSCA()
SetPrevious
Workbooks("WTNSystem").Worksheets("Calculation").Activate
End Sub
Sub GoToWDDB()
SetPrevious
Workbooks("WTNDatabase").Worksheets("Database").Activate
ActiveWindow.WindowState = xlMaximized
End Sub
Sub GoToWDOF()
SetPrevious
Workbooks("WTNDatabase").Worksheets("Offers").Activate
ActiveWindow.WindowState = xlMaximized
End Sub
Sub GoToWSCU()
SetPrevious
Workbooks("WTNSystem").Worksheets("Customers").Activate
ActiveWindow.WindowState = xlMaximized
End Sub

The main parts are Set Previous() and ReturnToPrevious() that sets the
current workbook just before switching or returning to next
workbook/worksheet.
All Return buttons in the different sheets use the same ReturnToPrevious
procedure.

Thanks again for all the good ideas you share with us
Mats


"sebastienm" wrote:

> Hi,
> You can use a small class to track the sheet Deactivate event of the
> Application object. In the Personal book:
> - Add a class module and call it ClsSheetTracker. Paste the following code
> ion this class module
> ' ------------------------------------------------------
> Option Explicit
>
> Private WithEvents App As Application
> Private mLastSheet As Object
>
> '------------------------------------
> Public Property Get LastSheet() As Object
> Dim s As String
> If mLastSheet Is Nothing Then
> Set LastSheet = Nothing
> Else
> On Error Resume Next 'capture automation error if book has been
> closed
> s = mLastSheet.Name
> If Err <> 0 Then
> Set mLastSheet = Nothing
> Set LastSheet = Nothing
> Else
> Set LastSheet = mLastSheet
> End If
> End If
> End Property
>
> Public Sub GotoLastSheet()
> If Not LastSheet Is Nothing Then
> On Error Resume Next 'if hidden book or other error
> LastSheet.Parent.Activate
> LastSheet.Activate
> End If
> End Sub
> '------------------------------------
>
> Private Sub App_SheetDeactivate(ByVal Sh As Object)
> Set mLastSheet = Sh
> End Sub
>
> Private Sub Class_Initialize()
> Set App = Application
> End Sub
>
> Private Sub Class_Terminate()
> Set App = Nothing
> End Sub
> '-----------------------------------------------------
>
> As you can see in the above code, when a sheet is Deactivated the mLastSheet
> variable is set, keeping track of that deactivated sheet.
> Also there is 2 public methods, one that returns the Last Sheet, the other
> one is a sub (GotoLastSheet) that activates the last sheet.
>
> - Now in a regular module, create a public variable of the above class and
> add a Sub that calls the GotoLastSheet of the above class.
>
> '----------------------------------------------------
> Option Explicit
> Public Tracker As ClsSheetTracker
>
> Sub GotoLast()
> Tracker.GotoLastSheet
> End Sub
> '----------------------------------------------------
>
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "Mats Samson" wrote:
>
> > Hi,
> > I have two workbooks (WTNSystem and WTNDatabase) open, with several sheets
> > in each workbook.
> > I’d like to create a Return-buttons in the sheets that takes me back to the
> > previous sheet, even if it was in the other workbook. The originating sheet
> > can be different each time too.
> > I know that Public variables aren’t particularly reliable but maybe there is
> > a solution?! Furthermore, I’d like to keep this code in the hidden
> > Personal.xls, so the code can be used by any sheet calling it.
> > Thanks
> > Mats
> >