Home > Macros > Basics > Buttons Excel Worksheet Macro Buttons Add navigation buttons to the top of each worksheet in an Excel file, to go to the previous or next sheet

 

Introduction

Add buttons on a worksheet to run macros that help you navigate -- a Back button and a Next button.

Watch this video to see the steps, and the written instructions are below the video.

Video Timeline:

• 00:00 Introduction
• 00:18 Navigation Macros
• 00:51 Add 1st Worksheet Button
• 01:40 Format Worksheet Button
• 02:20 Add 2nd Worksheet Button
• 02:43 Format 2nd Worksheet Button
• 03:07 Align and Resize Buttons
• 04:11 Copy Buttons to Other Sheets
• 04:41 Test Worksheet Buttons

How the Macros Work

There are two Excel VBA macros in the workbook -- one that takes you to the next sheet in the workbook, and one that takes you to the previous sheet. If you're on the third sheet, click the Next button to go to the fourth sheet. Or, click the Back button to go to the second sheet.

If the next sheet in the chosen direction 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

Copy the Navigation Code

First, copy this navigation code to a regular module in your workbook. Save your file as Macro-Enabled (*.xlsm) or Binary (*.xlsb).

'=================================
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

Add the Buttons

Now that the macros have been added to the workbook, you'll add the buttons.

Back Button

Format the Macro Button

Add the Next Button

Align the Macro Buttons

Add a Back Button

Now that the macros have been added to the workbook, you'll add the Back button on the first worksheet.

1. Select the first sheet in the workbook.
2. On the Ribbon, click the Developer tab
   • Note: If the Developer tab isn't visible, click the Office Button, then click Excel Options. Click the Popular category, then add a check mark to Show Developer tab in the Ribbon.
3. In the Controls group, click Insert, to see the Form Controls and ActiveX Controls.
4. In the Form Controls section, click Button.

   

5. On the worksheet, drag the pointer, to add a button of a specific size, or click to create a default sized button.
6. In the Assign Macro window that opens automatically, click on the GoSheetBack macro to select it, then click OK

Format the Macro Button

While the button is still selected, you can change its caption and add some formatting.

1. To change the button's caption, select the existing text, and type < Back.

 

2. On the Ribbon's Home tab, use the font formatting buttons, such as Bold and Font Size, to change the appearance of the button. In the screen shot below, the font size has been increased to 14.

 

Add the Next Button

Follow the same instructions as above, to add a Next button to the worksheet, with these changes.

• In the Assign Macro window, select the GoSheetNext macro
• For the caption, type Next >

Align the Macro Buttons

If the Back and Next buttons are not aligned, follow these steps (in Excel 2010) to align them:

1. Press the Ctrl key on the keyboard, and click on the Back button
2. Continue to press the Ctrl key, and click the Next button
3. On the Ribbon, under Drawing Tools, click the Format tab
4. Click Align, then click Align Top

 

Copy Buttons to Other Sheets

After the macro buttons are completed on the first worksheet, follow these steps to copy them onto the remaining worksheets.

1. Press the Ctrl key on the keyboard, and click on the Back button
2. Continue to press the Ctrl key, and click the Next button
3. On the Ribbon, click the Home tab, then click Copy
4. Select the next worksheet, click cell B1
5. On the Ribbon, click Paste (or use the keyboard shortcut, Ctrl + V)
6. Select the next worksheet, and paste into cell B1, and repeat for all the remaining sheets.

Test the Macro Buttons

After you've added all the buttons, you can test them.

To move through the sheets, click either the Back or Next button on any sheet.

Get the Sample File

You can download a copy of the Excel Worksheet Macro Buttons File to see the worksheet buttons and the worksheet navigation code. The file is in xlsm format, and is zipped. After you unzip the file and open it, enable macros, so you can use the navigation macros.

More Tutorials

Form Control Buttons

Getting Started with Excel Macros

Text Box Macros

FAQs, Excel VBA, Excel Macros  

Add Code to Excel Workbook