Run an Excel Macro With a Worksheet Button

In my workbooks, I sometimes add buttons to run macros. Usually, they’re for navigation to the next or previous sheet, or to run a macro that’s specific to the contents of the worksheet.

In this example, I’ve got two macros in the workbook – one that takes you to the next sheet in the workbook, and one that takes you to the previous sheet. So, if you’re on the second sheet, you can click the Next button to go to the third sheet. Or, click the Back button to go to the first sheet.

Add a Button

  1. 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.
  2. In the Controls group, click Insert, to see the controls from the Form toolbar and the Control Toolbox.
  3. In the Form Controls section, click Button.
    FormButton01
  4. Click on the worksheet to add a button, or drag on the worksheet to add a button of a specific size.
  5. In the Assign Macro dialog box that opens automatically, click on the name of a macro to select it, then click OK
    AssignMacro01

Format the 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 a caption to replace it. The border around the button will have diagonal lines when you’re editing the text
  2. ButtonBack01
  3. Click on the border of the button, to select it. The outline should change to a dotted pattern, as you can see in the next screenshot.
    ButtonBack02
  4. On the Ribbon’s Home tab, use the font formatting buttons, such as Bold and Font Size, to change the appearance of the button.

Use the Buttons

After you’ve added the Back button, repeat the steps to create a Next button. Then, copy the two buttons to any worksheets or chart sheets in the workbook.

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

ButtonBack03

The Navigation Code

Here’s the code that I used to make the buttons select the next or previous sheet.

Sub GoSheetNext()
Dim wb As Workbook
Dim lSheet As Long
Set wb = ActiveWorkbook
lSheet = ActiveSheet.Index

With wb
  If lSheet = .Sheets.Count Then
    .Sheets(1).Select
  Else
    .Sheets(lSheet + 1).Select
  End If
End With

End Sub

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

Sub GoSheetBack()
Dim wb As Workbook
Dim lSheet As Long

Set wb = ActiveWorkbook
lSheet = ActiveSheet.Index

With wb
  If lSheet = 1 Then
    .Sheets(.Sheets.Count).Select
  Else
    .Sheets(lSheet - 1).Select
  End If
End With

End Sub

________________

You may also like...

16 Responses

  1. Ron de Bruin says:

    Hi Debra

    Maybe add a test if the sheet is hidden before you use the select line ?

    The shortcuts Ctrl PgUp and Ctrl PgDown skip hidden sheets

  2. Thanks Ron, that’s a good idea. I’ll see what I can come up with.

  3. Jeffrey weir says:

    Or you could use hyperlinks instead of macros. Advantage is that they keep the workbook macro free (assuming this would be your only macro), and don’t wipe out undo functionality. I’ve been using them a lot recently for navigation round the mother of all spreadsheets, and put the forward and back navigation buttons on my quick access toolbar in Excel, which helps too.

  4. Ron de Bruin says:

    Hi Debra

    Did you see my comment about the start date of the 1904 date system ?

  5. Jeffrey weir says:

    On second thoughts, hyperlinks would be a pain for next/prev sheet navigation, because you’d need to set up 2 for each sheet, and if the sheet orders were changed the hyperlinks wouldn’t adjust accordingly, so you wouldn’t be going to the next sheet.

  6. Ron, yes, and thanks for the comment about the 1094 date system. I added a note to the article.

    Thanks Jeffrey, a menu sheet, with hyperlinks, can be useful at the front of a large workbook. This article was showing how to attach a macro to a button, so I tried to pick a simple task to demo that.

  7. Pooja says:

    Hi Ron de Bruin,

    8th July was my birthday… :)

  8. Bob R says:

    Debra – A little behind on my blog reading, but just did the macro button one. That means I followed your steps and actually created a workbook with the macros assigned to a button. Thank you! I always wondered how to do that, but never had a driving need.

  9. Thanks Bob! Glad to hear that you tried it, and were successful.

  10. […] How to Run an Excel Macro With a Worksheet Button Share and Enjoy: […]

  11. Julia says:

    I have made a user form with a next button.How do i modify the next code for cells (next record)instead of using the sheet as in your code

  12. Joseph says:

    Hello,
    I want to use macro by clicking a single button which will open a file.
    your help required.

  13. Hennie Neuhoff says:

    I’m desperate for some help on this. I’ve be using buttons on certain sheets to run my code. It’s been working for a number of years without any problems. Suddenly I can’t “activate” the button – it will not “fire”. I delete the button and tried to replace it. I get a message “Cannot insert object” with “OK” the only option.
    Any assistance will be much appreciated.

    • The problem is probably caused by a security update that Microsoft released in December – it caused problems with the ActiveX controls for many people.
      Microsoft has now provided a “Fixit” button that will remove the .exd files that are causing the problems, and it is available on this page:
      http://support.microsoft.com/kb/3025036/EN-US

      You could try that, or perhaps one of your IT people could run it.

  14. HENNIE NEUHOFF says:

    Thanks Debra. I manage to delete the files and it’s working again.

Leave a Reply to Create a dynamic stock chart using a web query and a drop down list in excel | Get Digital Help Cancel reply

Your email address will not be published. Required fields are marked *