VBA Express : Excel - Go To Previous Worksheet / Go To Next Worksheet

Excel

Go To Previous Worksheet / Go To Next Worksheet

Ease of Use

Easy

Version tested with

2000, 2002, 2003

Submitted by:

lucas

Description:

Buttons from the "forms" toolbar are assigned macro's for forward and back one sheet at a time.

Discussion:

You wish to navigate through a workbook one sheet at a time and occasionally need to go backwards. The buttons make navigation intuitive for people not familiar with excel sheet tabs.

Code:

instructions for use

			Option Explicit 
 
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

How to use:

Go to Tools-Macro-Visual Basic Editor from the main toolbar.
From the insert menu, click on module. Paste the code above into the module.
Save and close the Visual Basic Editor.
From the forms toolbar add a button to the first sheet, right click and assign the sub "GoForth" to the button.
On the next sheet add one button to go back, right click and assign the "GoBack" macro to it. And So on.

Test the code:

Click on the buttons to test.

Sample File:

previous-next.zip 8.67KB

Approved by mdmackillop

This entry has been viewed 225 times.