Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to previous worksheet
Hi,
I have two workbooks (WTNSystem and WTNDatabase) open, with several sheets in each workbook. Id 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 arent particularly reliable but maybe there is a solution?! Furthermore, Id like to keep this code in the hidden Personal.xls, so the code can be used by any sheet calling it. Thanks Mats |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to previous worksheet
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to previous worksheet
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. Id 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 arent particularly reliable but maybe there is a solution?! Furthermore, Id like to keep this code in the hidden Personal.xls, so the code can be used by any sheet calling it. Thanks Mats |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to previous worksheet
Thank you guys for all efforts!
But meanwhile I managed to solve the matter rather simple. Its maybe not as fancy as Sebastiens 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").Activa te End Sub Sub GoToWSCA() SetPrevious Workbooks("WTNSystem").Worksheets("Calculation").A ctivate End Sub Sub GoToWDDB() SetPrevious Workbooks("WTNDatabase").Worksheets("Database").Ac tivate ActiveWindow.WindowState = xlMaximized End Sub Sub GoToWDOF() SetPrevious Workbooks("WTNDatabase").Worksheets("Offers").Acti vate ActiveWindow.WindowState = xlMaximized End Sub Sub GoToWSCU() SetPrevious Workbooks("WTNSystem").Worksheets("Customers").Act ivate 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. Id 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 arent particularly reliable but maybe there is a solution?! Furthermore, Id like to keep this code in the hidden Personal.xls, so the code can be used by any sheet calling it. Thanks Mats |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return to previous cell? | Excel Discussion (Misc queries) | |||
Return to a previous cell | Excel Discussion (Misc queries) | |||
How do I return to a previous worksheet in Excel? | Excel Discussion (Misc queries) | |||
Return to previous worksheet after code pastes in another worksheet? | Excel Programming | |||
Macro to return to previous worksheet | Excel Programming |