Search Contextures Sites

Contextures
Excel news
by email

 

 

Learn how to create Excel dashboards.

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

Time-saving
Pivot Table add-in

 

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
Excel VBA Tutorials

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.

back and next buttons

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.

  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.

    Form Controls 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

    Assign Macro 

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.

     Button 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.

     Button Back

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

     Buttons Align

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.

    Buttons Copy

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.

back and next buttons

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.

Excel VBA Tutorials

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.

 

Last updated: July 30, 2013 9:33 AM