PDA

View Full Version : Forward and Backward Chronologically...



Scottie P
07-21-2004, 09:25 AM
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
07-21-2004, 10:47 AM
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)
07-21-2004, 01:01 PM
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
07-21-2004, 10:40 PM
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