Next and Previous Navigation Buttons

[seleseped]

I would like to have a "PREVIOUS" navigation button in a workbook that goes back to the previously activated worksheet, which may or may not be physically the previous worksheet in the workbook. In other words, if I go from Sheet #1, to Sheet #3, to Sheet #5, I'd like to return to Sheet #3 from Sheet #5 (rather then navigating back to Sheet #4).

I found this bit of code on another help listserve (my apologies if I'm violating some sort of sharing rules by copying it here) and was able to do as directed:

"Open VB editor and double click 'Thisworkbook' and paste this code in on the right:

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

Then right click 'ThisWorkbook' and insert module and paste this code in

Public lastsheet As String
Sub Select_Last()
Sheets(lastsheet).Select
End Sub

Whenever a user changes sheets, the name of the last sheet is now held in the variable 'Lastsheet' so if you assign a shortcut or button to the code then the last used sheet will be selected."

My problem is that when I want to assign the macro, I can't find macro name that looks anything like the above in the drop down list of Macro Names. Can anybody tell me how to assign the macro to a button? Or if you have other (maybe more simple) suggestions, I'd also appreciate those.

Thanking you in advance for your time and expertise.
Michele

 

[jim may]

The below code goes into a Standard Module, Not the Thisworkbook Module.

Public lastsheet As String

Sub Select_Last()
Sheets(lastsheet).Select
End Sub

 

[seleseped]

Jim,
Thank you for your help. As you suggested, I put this bit of code into Module 1 (in the 'Modules' folder - is that what you meant by "a Standard Module"?):

Public lastsheet As String
Sub Select_Last()
Sheets(lastsheet).Select
End Sub

The following bit of code is in my 'ThisWorkbook' module:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
lastsheet = Sh.Name
End Sub

I now can find the macro name (Select_Last) in my macro menu. However, after assigning the macro to a button and clicking the button, I get the message "run time error 9: subscript out of range." This line, "Sheets(lastsheet).Select" (from the first set of code above), is highlighted in the debugger. I know this should mean something to me but I'm not very literate in VB. I really appreciate your help.

Michele

 

[seleseped]

I would like to have a "PREVIOUS" navigation button in a workbook that goes back to the previously activated worksheet, which may or may not be physically the previous worksheet in the workbook. In other words, if I go from Sheet #1, to Sheet #3, to Sheet #5, I'd like to return to Sheet #3 from Sheet #5 (rather then navigating back to Sheet #4).

I found this bit of code on another help listserve (my apologies if I'm violating some sort of sharing rules by copying it here) and was able to do as directed:

"Open VB editor and double click 'Thisworkbook' and paste this code in on the right:

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

Then right click 'ThisWorkbook' and insert module and paste this code in

Public lastsheet As String
Sub Select_Last()
Sheets(lastsheet).Select
End Sub

Whenever a user changes sheets, the name of the last sheet is now held in the variable 'Lastsheet' so if you assign a shortcut or button to the code then the last used sheet will be selected."

My problem is that when I want to assign the macro, I can't find macro name that looks anything like the above in the drop down list of Macro Names. Can anybody tell me how to assign the macro to a button? Or if you have other (maybe more simple) suggestions, I'd also appreciate those.

Thanking you in advance for your time and expertise.
Michele

I have been successful in creating a PREVIOUS button which goes back to the worksheet activated just prior to the current one (using the code suggested in my first request for help). I'd like to now get more sophisticated with code that allows the user to continue going back to the next most recently activated sheet (in the same manner that using the back arrow in Internet Explorer and other browsers allow the user to keep going back to previous URLs until the very first one is reached).

In other words, if a user goes to sheet 1, then to sheet 3, then to sheet 7, then to sheet 5, I'd like to have code that would take the user in reverse order from sheet 5 to sheet 7 to sheet 3 and finally to sheet 1.

Thanking you in advance,
Michele

 

[jim may]

Excel's VBA will always BALK if you use the SELECT statement when Your Current active object is not the same object.

If you are currrently on Sheet2 (the ActiveSheet) using:

Sub Select_Last()
Sheets(lastsheet).Select
End Sub

Would surely produce a r/t error 9

 

[seleseped]

Thanks for your reply.

I actually am not having any troubles with balking. As an example, say I go from sheet 3 to 5 to 7. The macro nicely goes back from sheet 7 to sheet 5. The problem is that now the macro sees sheet 7 as the previous and so the macro returns to sheet 7 (now from sheet 5) rather than going back one more step to sheet 3. The end result is that I'm simply going back between sheets 5 and 7.

I think what I want the macro to do is hold the sheet names in the order they were activated so that the user can continue to work his way backward, in reverse order of activation, to the very beginning (sheet 1) if desired. However, I have no idea how to write code for that!

I am VERY novice with VB and so appreciate any handholding (and detailed advice).
Michele