Excel Design Solutions

Site
Web

You are here:

| Login

Excel Environment Templates Data AutoFilter Advanced Filter Pivot Tables Data Validation Names and Ranges Charts Formatting Formulae and Functions Date Lookup Math Logical Text User Defined VBA ADO FSO Forms, Toolbars and Controls User Defined Functions Regular Expressions Classes API Addins SQL

Recommended Blogs

Recommended Forums

Recommended Articles

Recommended Books

Can find what you are looking for? Try our custom Google search:

2 of 3

Navigate Sheets - Move between tabs with VBA

Author: Jon von der Heyden

Dated: 20/05/2010

Overview:

Some developers choose to hide sheet tabs in their Excel models. This presents the question 'how will my users navigate between sheets?'. Some choose to use hyperlinks on each sheet, some use a primary index sheet, or a combination of both. But some want a VBA method to move from the active sheet to the next/previous sheet.

Of course we can use the Next property, i.e. ActiveSheet.Next.Activate, and the Previous property, i.e. ActiveSheet.Previous.Activate. But this isn't always entirely reliable. If the workbook houses hidden sheets then the Next and Previous methods may not always work.

This article illustrates how to navigate from the active sheet to the next/previous visible sheet.

Always back-up your workbook before constructing and testing a routine that could alter your data and data structures. The 'Undo' command cannot be used to reverse the actions of a macro!

Please acquaint yourself with any comments in the code as these may provide much of the explanation. Remove the comments from your project when you are comfortable with the methods described; to maintain a tidy sub-routine.

This code belongs in a standard module.

Option Explicit
'! Written by Jon von der Heyden
'! Excel Design Solutions Ltd
'! www.exceldesignsolutions.com
'! May-2010
'! Excel 2007

Private Enum MoveDirection
    mdNext
    mdPrevious
End Enum

Private Sub ToggleBetweenSheets(ByRef mdDirection As MoveDirection)
    Dim lngShtIndex As Long, lngSheet As Long
    lngShtIndex = ActiveSheet.Index
    
    If mdDirection = mdNext Then
        For lngSheet = lngShtIndex + 1 To Sheets.Count
            If Sheets(lngSheet).Visible = -1 Then
                Sheets(lngSheet).Activate: Exit For
            End If
        Next lngSheet
    ElseIf mdDirection = mdPrevious Then
        For lngSheet = lngShtIndex - 1 To 1 Step -1
            If Sheets(lngSheet).Visible = -1 Then
                Sheets(lngSheet).Activate: Exit For
            End If
        Next lngSheet
    End If
End Sub

Public Sub Demo_ToggeBetweenSheets()
    Call ToggleBetweenSheets(mdDirection:=mdNext)
    'Call ToggleBetweenSheets(mdDirection:=mdPrevious)
End Sub

See Also:

External References:

http://msdn.microsoft.com/en-us/library/aa612998%28office.10%29.aspx

http://msdn.microsoft.com/en-us/library/aa160361%28office.10%29.aspx

2 of 3

home | articles | downloads | about | contact