Excel – VBA – Worksheet Macro Buttons

[wpfilebase tag=file path='01/SheetsNextBackCode.zip']

Introduction to Worksheet Macro Buttons

In an Excel workbook, you can add buttons on a [gs worksheet] to run Excel VBA [gs macro]s. In this example, navigation buttons are added to the top of each [gs worksheet] -- a Back button and a Next button.

There are two Excel VBA macros in the [gs 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.

[tab:Navigation]

Copy the Navigation Code

First, copy this navigation code to a regular [gs module] in your [gs 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

'=================================

[tab:Back Button]

Add a Back Button

Now that the [gs macro]s have been added to the [gs workbook], you'll add the Back button on the first worksheet.

1. Select the first sheet in the [gs workbook];
2. On the Ribbon, click the Developertab;
   • 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;

[tab:Next Button]

[tab:Align Buttons]

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 [gs 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;

[tab:Copy Buttons]

[tab:Test]

[tab:END]