Excel VBA Worksheet Macro Buttons

Search Contextures Sites

Excel VBA Worksheet Macro Buttons
Introduction to Worksheet Macro Buttons
Copy the Navigation Code
Add a Back Button
Format the Macro Button
Add the Next Button
Align the Macro Buttons
Copy Buttons to Other Sheets
Test the Macro Buttons
Download the Excel Macro Buttons File
Watch the Excel Macro Buttons Video

Introduction to Worksheet Macro Buttons

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

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 a Back Button

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

Select the first sheet in the workbook.

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.

In the Controls group, click Insert, to see the Form Controls and ActiveX Controls.

In the Form Controls section, click Button.

On the worksheet, drag the pointer, to add a button of a specific size, or click to create a default sized button.

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.

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

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:

Press the Ctrl key on the keyboard, and click on the Back button

Continue to press the Ctrl key, and click the Next button

On the Ribbon, under Drawing Tools, click the Format tab

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.

Press the Ctrl key on the keyboard, and click on the Back button

Continue to press the Ctrl key, and click the Next button

On the Ribbon, click the Home tab, then click Copy

Select the next worksheet, click cell B1

On the Ribbon, click Paste (or use the keyboard shortcut, Ctrl + V)

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.
Download the Excel Macro Buttons 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 Excel 2007 format, and is zipped. After you unzip the file and open it, enable macros, so you can use the navigation macros.

Watch the Excel Macro Buttons Video

To see the steps for creating the navigation macro buttons, please watch this Excel tutorial video.
Home Excel Tips Excel Files The Excel Store Blog Contact

Privacy Policy

Contextures Inc., Copyright ©2011
All rights reserved.

Debra Dalgleish

Last updated: January 27, 2011 1:27 PM

	Introduction to Worksheet Macro Buttons Copy the Navigation Code Add a Back Button Format the Macro Button Add the Next Button Align the Macro Buttons Copy Buttons to Other Sheets Test the Macro Buttons Download the Excel Macro Buttons File Watch the Excel Macro Buttons Video Introduction to Worksheet Macro Buttons In an Excel workbook, you can add buttons on a worksheet to run Excel VBA macros. In this example, navigation buttons are added to the top of each worksheet -- a Back button and a Next button. 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 a Back Button Now that the macros have been added to the workbook, you'll add the Back button on the first worksheet. Select the first sheet in the workbook. 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. In the Controls group, click Insert, to see the Form Controls and ActiveX Controls. In the Form Controls section, click Button. On the worksheet, drag the pointer, to add a button of a specific size, or click to create a default sized button. 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. To change the button’s caption, select the existing text, and type < Back. 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: Press the Ctrl key on the keyboard, and click on the Back button Continue to press the Ctrl key, and click the Next button On the Ribbon, under Drawing Tools, click the Format tab 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. Press the Ctrl key on the keyboard, and click on the Back button Continue to press the Ctrl key, and click the Next button On the Ribbon, click the Home tab, then click Copy Select the next worksheet, click cell B1 On the Ribbon, click Paste (or use the keyboard shortcut, Ctrl + V) 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.


	Download the Excel Macro Buttons 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 Excel 2007 format, and is zipped. After you unzip the file and open it, enable macros, so you can use the navigation macros. Watch the Excel Macro Buttons Video To see the steps for creating the navigation macro buttons, please watch this Excel tutorial video.