Return to Previously Selected Sheet

Location: http://www.mvps.org/dmcritchie/excel/sheetback.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Provide ability to Return to the previous sheet, or forward to the next sheet like Back and Forward buttons in Browser

Macros for this

Haven't tested this out for interference in trying to use same code with multiple workbook, or from use of hyperlinks. For the moment will consider Hyperlinks as a separate thing because a hyperlink would create a forward then you return and the forward would no longer be to another sheet but to the hyperlinked item.
Don't think this code can be used in personal.xls file because believe public dimension applies to all of Excel in the current session. So for mutliple workbooks you would have to at least include the workbookname, or modify the subroutines for each workbook so they don't conflict with one another.
For toolbar icon implementation will be using short stubby RED arrows and they will be added on workbook_activate.
More information on Event macros.
Also have to add code for renaming of worksheets. Don't see an event for this but if there is on the code would be...
 
    nxtsheets(nxtsheets(0)) = ActiveSheet.Name
Suggestions (solutions) to make this more generic for use in any workbook are welcome.
The following has been briefly tested and then posted on June 04, 2004.
'---------- the following into  ThisWorkBook
Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  'D.McRitchie,  2004-06-04   sheetback.htm 
  nxtsheets(0) = nxtsheets(0) + 1
  nxtsheets(nxtsheets(0)) = ActiveSheet.Name
End Sub

'---------  the following into  a regular module (i.e. module1) 
'---------     in your workbook 
Option Explicit
Public nxtsheets(100) As Variant
Sub BackBy_nxtsheets()
    'D.McRitchie,  2004-06-04   sheetback.htm
    If nxtsheets(0) < 2 And nxtsheets(100) = "" Then
       MsgBox "can't go back via nxtsheets"
       Exit Sub
    End If
    Worksheets(nxtsheets(nxtsheets(0) - 1)).Activate
    nxtsheets(0) = nxtsheets(0) - 2
    '--     1 was subtracted from nxtsheets
End Sub

Sub ForwardBy_nxtsheets()
    'D.McRitchie,  2004-06-04   sheetback.htm
    If nxtsheets(0) = 100 Then nxtsheets(0) = 0
    If nxtsheets(nxtsheets(0) + 1) = "" Then
          MsgBox "can't go Forward via nxtsheets"
       Exit Sub
    End If
    Worksheets(nxtsheets(nxtsheets(0) + 1)).Activate
    '--   1 was added to nxtsheets
End Sub

Event Macros for event macros the one going into ThisWorkBook.
Getting Started with Macros for regular macros.
Hyperlinks within buildtoc.htm
Toolbar Buttons

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions. Posting suggestions and netiquette. More information on newsgroups and searching newsgroups. Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).

This page was introduced on June 04, 2004. [My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie send email comments

Return to Previously Selected Sheet

Macros for this

Related (#related)