How to make a button for a macro in Excel

Written by sue smith Google
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to make a button for a macro in Excel
Macros provide spreadsheet users with shortcuts to processes such as filtering data sets. (Microsoft Corporation)

In Microsoft Excel, users can set worksheets up to make their regular spreadsheet tasks easier. If you find that you are carrying out the same process or set of processes often within the same worksheet, you can create a macro for it to encapsulate those actions. You can then add a button to the spreadsheet and assign the macro to it. This allows the spreadsheet creator to carry out the same process simply by pressing the button, as well as making the process available to other users of the spreadsheet, which can be useful within organisations with shared data resources.

Creating macros

Excel users can create macros to use within spreadsheets. With a spreadsheet open in Excel, the user can carry out the actions they want the macro to store, recording them for storage as a macro. To do so, the user must show the Developer tab by choosing "File," "Options," "Customize Ribbon" and select the "Developer" checkbox from the "Main Tabs" section. You can enable macros in Excel by choosing "Macro Security" from the "Code" section of the Developer tab, selecting "Macro Settings" and "Enable all macros." Choosing "Record Macro" and entering a name for the macro lets the user get started recording it, selecting a workbook and entering a description. On clicking "OK" the user can then carry out the actions they want recorded, choosing "Stop Recording" when finished.

Adding a button

Excel users can add buttons to worksheets, later creating links to such buttons including defined macros. In the Developer tab, selecting "Insert" from the "Controls" section will present the option to add user input controls. To add a button, the user can choose "Button" in the "Form Controls" section. Clicking wherever you want the button to appear within the worksheet will instantly add it. The point at which you click will represent the top left corner of the button. The user can alter the button properties once it is set up to link to the macro.

Assigning a macro to a button

Assigning a macro to a button is partly automated in Excel when a button is added to a worksheet. When you add a button to a worksheet, the "Assign Macro" control should appear. This control should list any macros that have been added. The user can link the macro to the button by selecting it by the name chosen when creating it. Clicking "OK" will link the macro to the button control. If the user wants to edit any properties of the button, they can do so by right-clicking it and selecting "Format Control." This allows you to set size, protection and various other properties for buttons or any other form control elements you use in your worksheets. The final button should execute the processes that were recorded as part of the macro.


Anyone who programs in Visual Basic can define macros using VBA syntax. It is also possible to add ActiveX controls using a similar process to linking macros to buttons within Excel spreadsheets. Enabling all macros for a worksheet can result in dangerous or unpredictable behaviour, so data should ideally be backed up before users have access to macro buttons.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.