Add Navigation Buttons to Excel Worksheet

Last July, I posted sample Excel VBA code to navigate to the next or previous worksheet. If you're on the second sheet, you can click the Next button to go to the third sheet. Or, click the Back button to go to the first sheet.

macrobutton09

In the comments for that blog post, Ron de Bruin suggested modifying the two navigation macros, so they test if the target sheet is hidden, before selecting it. Finally, only 14 months later,  the revised code is ready. Hey, quality work takes time! ;-)

Excel Worksheet Navigation Code

Here's the Excel VBA code for the two macros -- GoSheetBack and GoSheetNext. If the next sheet is hidden, the code keeps going until it finds the next visible sheet. If it reaches the end of the sheet tabs in either direction, it jumps to the other end, and continues from there.

'==========================
Sub GoSheetNext()
Dim wb As Workbook
Dim lSheets As Long
Dim lSheet As Long
Dim lMove As Long
Dim lNext As Long

Set wb = ActiveWorkbook
lSheets = wb.Sheets.Count
lSheet = ActiveSheet.Index
lMove = 1

With wb
  For lMove = 1 To lSheets - 1
    lNext = lSheet + lMove
    If lNext > lSheets Then
      lMove = 0
      lNext = 1
      lSheet = 1
    End If

    If .Sheets(lNext).Visible = True Then
      .Sheets(lNext).Select
      Exit For
    End If
  Next lMove

End With

End Sub
'==========================
Sub GoSheetBack()
Dim wb As Workbook
Dim lSheets As Long
Dim lSheet As Long
Dim lMove As Long
Dim lNext As Long

Set wb = ActiveWorkbook
lSheets = wb.Sheets.Count
lSheet = ActiveSheet.Index
lMove = 1

With wb
  For lMove = 1 To lSheets - 1
    lNext = lSheet - lMove
    If lNext < 1 Then
      lMove = 0
      lNext = lSheets
      lSheet = lSheets
    End If

    If .Sheets(lNext).Visible = True Then
      .Sheets(lNext).Select
      Exit For
    End If
  Next lMove
End With

End Sub
'==========================

Download the Sample File

To see the detailed instructions, and to download the sample Navigation code workbook, please go to the Excel VBA Worksheet Macro Buttons page on the Contextures website.

Watch the Excel Tutorial Video

To see the steps for creating the navigation macro buttons, please watch this Excel tutorial video.

____________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

8 comments to Add Navigation Buttons to Excel Worksheet

  • Rick Rothstein (MVP - Excel)

    We can shorten those two macros a little bit...

    Sub GoSheetNext()
    Dim X As Long
    X = ActiveSheet.Index
    Do
    X = (X + 1) Mod (Sheets.Count + 1)
    Loop While Not Sheets(X - (X = 0)).Visible
    Sheets(X - (X = 0)).Activate
    End Sub

    Sub GoSheetBack()
    Dim X As Long
    X = ActiveSheet.Index
    Do
    X = (X - 1) Mod (Sheets.Count + 1)
    Loop While Not Sheets(X - Sheets.Count * (X = 0)).Visible
    Sheets(X - Sheets.Count * (X = 0)).Activate
    End Sub

  • Martin

    Wow, Debra !!!
    Wow, Rick !!!

    a little question: Why the (X=0) ???

    Rgds,

    Martin

  • Rick Rothstein (MVP - Excel)

    @Martin,

    The (X=0) is a logical expression that will evaluate to True only when X is equal to zero and will be False the rest of the time. In VB, True has a numerical equivalent of -1 (by the way, this is the opposite of TRUE on a worksheet where it has a numerical equivalent of +1) and False has a numerical equivalent of -1. Inside the loop, X is calculated using the Mod operator... one is added to the current sheet index and then it compared to the number of sheets plus one... when they are equal, the Mod operator returns 0, but you cannot have a sheet index of 0, so only when X=0 do we want to add one to get it up to the minimum sheet index value allowed. When X=0, the expression inside the Do While statement becomes 0-(-1) which equals +1. For all other value, X gets returned as calculated because (X=0) will evaluate to zero.

  • Rick Rothstein (MVP - Excel)

    @Everyone,

    I think these modified versions of the macros I posted earlier are slightly better (it removes the duplicated calculation when the loop ends)...

    Sub GoSheetNext()
    Dim X As Long
    X = ActiveSheet.Index
    Do
    X = ((X + 1) Mod (Sheets.Count + 1)) - (X = Sheets.Count)
    Loop While Not Sheets(X).Visible
    Sheets(X).Activate
    End Sub

    Sub GoSheetBack()
    Dim X As Long
    X = ActiveSheet.Index
    Do
    X = ((X - 1) Mod (Sheets.Count + 1)) - Sheets.Count * (X = 1)
    Loop While Not Sheets(X).Visible
    Sheets(X).Activate
    End Sub

  • Martin

    what a VBA lesson!!!! (I was tempted to write "VBA class", but it could have been misleading....)

    Thanks so much, Rick!!

  • Rick Rothstein (MVP - Excel)

    @Martin,

    You are welcome. I hope you were able to read through the typo I just noticed in my first answer to you. I wrote "False has a numerical equivalent of -1? which was supposed to have said "False has a numerical equivalent of 0?. Sorry for any confusion this may have caused to you (or others).

  • Martin

    Yes, I did, but thanks anyway for pointing that out.

  • julia

    Hi Rick,
    how can I modify to use for a cell instead of sheet. I have a user form with a next button but no code

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>