Provide ability to Return to the previous sheet, or forward to the next sheet like Back and Forward buttons in Browser
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.NameSuggestions (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
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved