Home > Macros > Basics > Buttons Excel Worksheet Macro ButtonsAdd navigation buttons to the top of each worksheet in an Excel file, to go to the previous or next sheet |
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:
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
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
Now that the macros have been added to the workbook, you'll add the buttons.
Now that the macros have been added to the workbook, you'll add the Back button on the first worksheet.
While the button is still selected, you can change its caption and add some formatting.
Follow the same instructions as above, to add a Next button to the worksheet, with these changes.
If the Back and Next buttons are not aligned, follow these steps (in Excel 2010) to align them:
After the macro buttons are completed on the first worksheet, follow these steps to copy them onto the remaining worksheets.
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.
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.
Getting Started with Excel Macros
Last updated: May 14, 2023 1:19 PM