Forward and Backward Chronologically...

[Scottie P]

Since the topic of a 'back button' came up in another thread I thought that I would put this one out there as well. Again, I know that this is another shameless lifting from somewhere...

The two subs below will move the user through worksheets forward or backward, but there is a snag with this one: if there are hidden sheets we do a Runtime '1004' : Select method of Worksheet class failed.


' Move Forward

Sub goFwd()
    On Error Resume Next
    ActiveSheet.Next.Select
    If Err.Number <> 0 Then
    Sheets(1).Select
   Err.Clear
    End If
    On Error GoTo 0
End Sub

' Move Backward

Sub goBk()
    On Error Resume Next
    ActiveSheet.Previous.Select
    If Err.Number <> 0 Then
    Sheets(Sheets.Count).Select
    Err.Clear
    End If
    On Error GoTo 0
End Sub

Rather than keep this as is, can we set this up as global - I could create a CommandBar for this one.
Why not use Sheet tabs or Cntl + PageUp/PageDown, etc...? Because sometimes custom apps won't allow it...plus it would be a fun gizmo to have, of course!

Scott

[TonyJollans]

Well, I'm not sure about custom apps hijacking standard key combinations (unless they offer something you can't live without, I would avoid them) but what about something like this ..

Sub goFwd() 
On Error Resume Next
Dim iSheetNum as Integer
isheetnum = ActiveSheet.Index
Do
    Err.Clear
    isheetnum = isheetnum + 1
    if isheetnum > sheets.Count Then iSheetNum = 1
    Sheets(iSheetNum).Select
Loop Until Err.Number = 0
End Sub

[Richie(UK)]

Hi,

Shamelessly stealing Tony's code (it is copyright free, right? ) we could have one sub to suit the two objectives, with two small 'calling' subs.

Like this:

Sub GoForth()
    MoveSheet (1)
End Sub

Sub GoBack()
    MoveSheet (-1)
End Sub

Sub MoveSheet(iMove As Integer)
    Dim iSheetNum As Integer
iSheetNum = ActiveSheet.Index
    On Error Resume Next
    Do
    Err.Clear
    iSheetNum = iSheetNum + iMove
    If iMove > 0 Then
        If iSheetNum > Sheets.Count Then iSheetNum = 1
    Else
        If iSheetNum < 1 Then iSheetNum = Sheets.Count
    End If
    Sheets(iSheetNum).Select
    Loop Until Err.Number = 0
End Sub

[Scottie P]

Hello Gentlemen.
Thank you for the ideas...I will try these out in the morning. I've been busy with other stuff and haven't had a lot of time for puttering.

Scott