Introduction
Open the sample file, admin sheet, add macro to drop down, change a button macro, add your own macros, find button images, create an add-in.
More Ribbon Resources
Get the Sample File
Related tutorials.
In this tutorial, you'll see how to add your macros to a custom tab on the Excel Ribbon. The My Macros sample file has a custom tab with 10 buttons, and 2 drop down lists. You can choose label text, button images, and which macro each button should run.
The sample file also contains sample macros, to demonstrate how the custom tab works. Later, you can remove the sample macros, and add your own macros to the file.
To add your macros, you'll make changes on the worksheet. You do NOT need to go into the Ribbon CustomUI, or do any Ribbon programming.
NOTE : If you want to create your own custom tabs from scratch, see these pages:
Excel Ribbon -- Getting Started
- Excel Ribbon -- Add Custom Tab
To see how to add your macros to the ribbon, get the sample workbook , and watch this video. Written instructions are below the video.
Video Timeline
- 00:00 Intro
- 00:26 Getting Started
- 02:02 Test the Macros
- 01:36 Add Macro to Drop Down List
- 04:53 Change a Macro Button
- 06:29 Test the Revised Button
- 06:47 Copy Your Macro Code
- 08:50 Find a Button Image
- 11:51 Save As an Add-In
- 13:22 Get the Sample File
Follow these steps to get started with the Custom Ribbon tab.
- First, go to the Download section below, and get the My Macros sample file.
- Due to Microsoft security settings, you might need to unblock the downloaded file. Right-click on the zipped file, click Properties, and then check the box to Unblock.
- Next, unzip the downloaded folder, and store the Excel file in a folder on your computer.
- Open the Excel file, and be sure to enable macros.
Test the Custom Tab
After you open the My Macros sample file, you should see a custom tab on the Excel Ribbon -- MY MACROS. The custom tab has buttons and drop down lists, described below.
There are 10 buttons at the left, in 2 groups - Macro Buttons1 and Macro Buttons2.
To test the macros, click the Hello Message button, at the left side of the MY MACROS tab.
That button runs a macro that shows a message box, with the word, "Hello".
Click the OK button to close the message box.
Drop Down Lists
In the centre of the MY MACROS tab, there are 2 drop down lists -- Macro List 1, and Macro List 2.
Click the drop down arrow on Macro List 1, and in the drop down menu, click ToggleGridlines.
That runs the ToggleGridlines macro, which changes the gridlines setting on the active worksheet.
- If gridlines were off, it shows the gridlines.
- If gridlines were on, it hides the gridlines.
At the right of the MY MACROS tab, there are two command buttons for Excel Help.
Click those buttons to go to the main page of my Contextures website, or to this instruction page for the custom MY MACROS tab.
To run different macros from the MY MACROS Ribbon tab, you will make changes on the Admin sheet in the sample workbook.
The Button information is stored in the table at the left side of the Admin sheet.
In the second row, you can see the settings for Btn2. It has the label "Hello Message", and runs the HelloMsg macro. It displays the image named WebGoBack.
At the right, there are two Excel tables. They contain the macro names for each of the drop down lists.
In Macro List 1, you can see the ToggleGridlines macro, which you tested earlier.
The drop down lists are flexible, and will show all the items from the Macro List tables on the worksheet. You can delete items from the worksheet lists, add new items, or change the existing macro names.
Get Ready to Add a Macro
For the first change, you will add another macro to Macro List 1. Before you add the new macro, take a look at the current list:
- In the MY MACROS tab, click the arrow for Macro List 1 drop down.
- The 3 items in that drop down list are an exact match for the 3 items in the worksheet list for Macro List 1.
Add Another Macro
Next, you'll add another one of the sample macros to the Macro List 1 drop down.
- Click in cell G10, and type the macro name -- ToggleZeros .
- The table will automatically expand, to include the new row.
- On the Ribbon, click the Macro List 1 drop down again -- the Toggle Zeros macro hasn't been added there yet.
- To force the list to update, click on one of the other macros, such as ToggleRefStyle
- Then, click the arrow to see the list again --ToggleZero is now in the list.
There are 10 buttons set up in the MY MACROS tab. They can be customized, but there are some restrictions on the button changes.
- You CAN change the button labels, button images, and the macros that the buttons run.
- Do NOT change the names in the ButtonID column of the worksheet list.
- Do NOT add or delete buttons from the worksheet list
Note : If you want to add or remove buttons, you will have to modify the CustomUI file, to add them. See how to work with the CustomUI file .
Change a Button
For the first change, you will change the settings for Button 3. Currently, that button has an "X" image, and, and runs the TestMsg macro
- Label: Colour List
- Macro: ColourList
- Image: FontColorCycle
- Save the My Macros sample file
- Close the My Macros sample file, and then re-open it.
- Button 3 should now show its new label and image. Click the Colour List button, to add a new sheet to the workbook, with a list of colours.
The My Macros sample file contains a few macros, to show how the custom Ribbon tab works. You can keep some or all of those macros, or delete all of them, and put your own macros into the workbook.
First, copy the macro code that you want to add to the My Macros workbook. (For details on how to copy macro code to a regular module, see these instructions .)
You could use macro code from one of your Excel files, or find sample code on an Excel website. There is sample macro code below ( ListAllNames ), that you can use as a test.
After you copy the macro code, follow these steps, to paste your macros into the code modules:
- In Excel, press Alt+F11, to open the Visual Basic Editor (VBE)
- In the Project Explorer list, find the MyMacrosCustomTab project
- DO NOT change anything on the RibbonMacros module or the MacrosCtx module.
- You can add or remove macros on the ButtonMacros, Macros or Macros2 modules
- You can also insert new modules, and paste your macro code there.
- When you're finished, close the VBE, and return to Excel
Sample Macro Code
(Optional) If you don't have your own macro code to test, copy this macro code, and then follow the instructions above , to add it to the My Macros workbook. This macro adds a new sheet in your workbook, with a list of all the names in that workbook. Or, if there are no names, it shows a message.
Update the Admin Sheet
After you finish adding your macros, go to the Admin sheet and add your macro names.
- You can change the Button list, and replace the sample macro names and labels with your macros. Be sure to use the exact names for the macros that you added to the workbook.
- See the next section, for details on how to find image names to use with your macros
- You can add your macros to either of the Macro List tables
Remember to save your file , after making the changes
In the Button list, an image name is assigned to each of the buttons. After you add your own macros to the workbook, you might want different images, to match the actions in your macros.
Ribbon Commands
Image gallery add-ins.
For the My Macros sample file, I chose simple images, like the arrows and a plus sign. To find those images, I checked the commands in the Customize the Ribbon window. This is a quick and easy way to find built-in images for Excel.
Follow these steps to find different images for your buttons:
- Right-click on a blank part of the Excel Ribbon, and click Customize the Ribbon
- By default, Excel shows a list of Popular Commands
- To see different commands, click the arrow for the "Choose commands from" drop down
- Choose Commands Not in the Ribbon -- I like to use these images, because they're different from the ones that are already in use
- Scroll through the list, to find an image that you want to use for one of your macro buttons
- Then, to find the official name for the image, point to the command, and a popup will appear, with information about that command
- At the end of the command, in round bracket, is the image name
- Make a note of that name, so you can add it to the Button List. You will need to use the exact spelling , so write it down carefully, with the same upper and lower case .
- After you have found all the image names that you need, click the OK button, to close the Excel Options window.
- Then, add those image names, exactly , to the BtnImage column on the Admin sheet.
Another way to find built-in button images is with Image Gallery addins. For example:
- Download and install the 2007 Office System Add-In: Icons Gallery from Microsoft:
- After installing the gallery, open Excel and click the Developer tab.
- A new group appears at the right end of the tab, with 9 galleries.
- Point to any icon, to see its name.
- You can use that name when setting up your custom Ribbon tab. Be sure to use the exact spelling, with the same upper and lower case letters.
The My Macros sample file is saved in xlsm format, and its custom Ribbon tab is only visible when the sample workbook is active. If you want to use your macros in any open workbook, follow these steps to save the file as an Excel add-in.
Save File as Add-In
You can create your own add-ins, by storing macros in a file, then saving it as an add-in:
- Record or create one or more macros in the file
- On the Excel Ribbon, click the File tab, and click Save As
- Click the Browse button, to open the Save As window (stay in the selected folder)
- At the bottom, type a File Name
- In the Save As Type drop down, select Excel Add-In (*.xlam)
- The AddIns folder should be automatically selected
- Click Save, then close Excel
- To use your add-in file, follow the steps below, to install it
Install Your Add-in
After you have saved your file as an add-in, follow these steps to install it.
- Open Excel, and on the Ribbon, click the Developer tab (if it's missing, follow these instructions to show it)
- Click the Add-ins button.
- In the Add-in dialog box, find the My Macros Custom Ribbon Tab add-in, and add a check mark to its name.
- Click OK, to close the Add-ins window.
- The custom tab -- MY TOOLS -- should appear on the Ribbon.
- Click the MY TOOLS tab, and use the buttons and drop down lists to run your macros
Make Changes to Your Add-in
When you save a file as an Add-in, all of its worksheets are automatically hidden. If you want to make changes to the button settings or the macro lists, follow these steps:
- Click the + beside Microsoft Excel Objects, to see the objects
- Click on ThisWorkbook, to see its properties in the Properties window
- In the Properties list, scroll down to find the IsAddin property
- Change the IsAddin property to False
- Go back to Excel, and the workbook's sheets will be visible again
- Make your changes to the settings on the Admin sheet
NOTE : You will not be able to save the workbook while you make these changes. Go to the next section to see how to save.
Save the Add-in Changes
WARNING : When you close Excel, you might not get the usual warning, asking if you want to save your changes. After your changes are completed, follow these steps to save your changes.
- Change the IsAddin property to True
- Click the Save button at the top of the VBE window
- Close VBE, and go back to Excel
Excel Ribbon Resources
Thanks to the following people, who have shared their knowledge about Excel Ribbon customization.
- Ron de Bruin, Excel MVP, for the helpful Ribbon customization information on his website
- Ken Puls, Excel MVP, and co-author of RibbonX: Customizing the Office 2007 Ribbon - a great reference book.
- Greg Maxey, Word MVP, who posted Ribbon customization examples for Microsoft Word. There is a download link for his sample file, in the blue paragraph near the end of this page
To try the Ribbon custom macros tab, get the My Macros sample file . The zipped file is in xlsm format, and contains macros. After you add your macros, and change the button settings, you can save the file in xlam format, to create your own Macros add-in.
Excel Ribbon - Custom Tab
Customize Quick Access Toolbar (QAT)
Create a UserForm With ComboBoxes
VBA Code, Copy to a workbook
Last updated: November 20, 2023 12:12 PM
Excel VBA: Adding custom Button to the Toolbar or Ribbon
Custom buttons are added to the toolbars or ribbons when a Microsoft Excel sheet is opened, and removed when the sheet is closed. To be notified when a given Excel sheet is loaded we need to listen to the Workbook_Open event of the ThisWorkbook object found in the VBA Project explorer.
Please notice that the following code works best with Excel versions prior to Office 2007. If used in newer versions, from Excel 2007, the button and commandbar will be added to the “Add-Ins” tab. It is only possible to add buttons to the main ribbon using dynamic XML when using VBA. See below for more information.
The custom event called OnDoSomething must be defined in a global Module . It doesn’t work to define the method in the Workbook class. Insert a new Module and add the following code:
You can change the button icon by specifying another FaceId value. To get a list of all available FaceIds on you computer, download and and install the FaceID Browser:
- FaceID Browser
Excel 2007 and later To dynamically add buttons to the Ribbon you must use a combination of XML and VBA. For more information, please visit:
- How to: Use VBA to Add a Custom Command to the Ribbon
- Excel VBA: Adding items to the right-click menu
- Get Microsoft Excel
One thought on “ Excel VBA: Adding custom Button to the Toolbar or Ribbon ”
- Pingback: Excel VBA: Adding custom buttons to the Right-Click popup menu - Coragi
Leave a Reply Cancel reply
Your email address will not be published. Required fields are marked *
Save my name, email, and website in this browser for the next time I comment.
Related Posts
Attendance Tracker Template for Microsoft Excel
Employees and their managers often need to know how many vacation days they have left, how many sick days they’ve used, and whether or not they have personal days left. The user-friendly attendance tracker template from Coragi makes it possible for you to record attendance as well as a summary of key data for a […]
Employee Absence tracking Template for Microsoft Excel
From time to time, every employee will be absent from work. Companies must deal with sick leave in some form or another for a variety of reasons. Absence might range from a minor cold that clears up in a few days to a lengthy sick leave that has a significant influence on a team’s performance […]
Excel VBA: Get Employee information from Active Directory
If you manage people in your organization, either as a leader or a project manager, the Microsoft Active Directory (AD) is a gold mine of valuable information open to anyone inside the corporate network. Active Directory (AD) is a directory service for Windows domain networks. It was original designed to keep track of computers and […]
Subscribe to our newsletter
Email address:
By checking this box, I consent to receive e-mails from Coragi with product- and company updates, promotions, and tips & tricks.
Privacy Overview
- Ablebits blog
- Excel macro
How to run macro in Excel and create your own macro button
In this tutorial, we'll cover many different ways to run a macro in Excel - from the ribbon and VB Editor, with a custom keyboard shortcut, and by creating your own macro button.
Though running an Excel macro is a simple thing for experienced users, it might not be immediately obvious to beginners. In this article, you will learn several methods to run macros, some of which may completely change your way of interacting with Excel workbooks.
How to run a macro from Excel ribbon
One of the fastest ways to execute VBA in Excel is to run a macro from the Developer tab. If you have never dealt with VBA code before, you may need to activate the Developer tab first. And then, do the following:
Tip. If the Developer tab is not added to your Excel ribbon, press Alt + F8 to open the Macro dialog.
Run a macro with custom keyboard shortcut
If you execute a certain macro on a regular basis, you can assign a shortcut key to it. A shortcut can be added while recording a new macro and to an existing one. For this, carry out these steps:
- On the Developer tab, in the Code group, click Macros .
- For lowercase letters, the shortcut is Ctrl + letter .
- Close the Macro dialog box.
Tip. It is recommended to always use uppercase key combinations for macros ( Ctrl + Shift + letter ) not to override the default Excel shortcuts. For example, if you assign Ctrl + f to a macro, you will lose the ability to call the Find and Replace dialog.
How to run macro from VBA Editor
If you aim to become an Excel pro, then you should definitely know how to start a macro not only from Excel, but also from the Visual Basic Editor. The good news is that it's a lot easier than you might expect :)
- Press Alt + F11 to launch the Visual Basic Editor.
- In the Project Explorer window on the left, double-click the module containing your macro to open it.
- On the menu bar, click Run > Run Sub/UserForm .
- On the toolbar, click the Run Macro button (green triangle).
Alternatively, you can use one of the following shortcuts:
- Press F5 to run the entire code.
- Press F8 to run each code line separately. This is very useful when testing and debugging macros.
Tip. If you like operating Excel from you keyboard, this tutorial may come in handy: 30 most useful Excel keyboard shortcuts .
How to create a macro button in Excel
The traditional ways of running macros are not hard, but still might present a problem if you are sharing a workbook with someone who has no experience with VBA - they simply won't know where to look! To make running a macro really easy and intuitive for anyone, create your own macro button.
- Click anywhere in the worksheet. This will open the Assign Macro dialogue box.
- If the text does not fit in the button, make the button control bigger or smaller by dragging the sizing handles. When finished, click anywhere on the sheet to exit the edit mode.
Tip. You can also assign a macro to an existing button or other Form controls such as spin buttons or scrollbars. For this, right-click the control inserted in your worksheet and choose Assign Macro from the pop-up menu.
Create a macro button from a graphic object
Regrettably, it is not possible to customize the appearance of button controls, because of which the button we created a moment ago does not look very nice. To make a really beautiful Excel macro button, you can use shapes, icons, images, WordArt and other objects.
As an example, I'll show you how you can run a macro by clicking a shape:
- In your worksheet, click where you want to insert the shape object.
- Format your shape-button the way you want. For example, you can change the fill and outline colors or use one of the predefined styles on the Shape Format tab. To add some text to the shape, simply double-click it and start typing.
How to add a macro button to Quick Access Toolbar
The macro button inserted in a worksheet looks good, but adding a button to each and every sheet is time-consuming. To make your favorite macro accessible from anywhere, add it to the Quick Access Toolbar . Here's how:
- Right-click the Quick Access Toolbar and choose More Commands… from the context menu.
- In the Choose commands from list, select Macros .
- Click OK twice to close both dialog windows.
How to put a macro button on Excel ribbon
In case you have a few frequently used macros in your Excel toolbox, you may find it convenient to have a custom ribbon group, say My Macros , and add all popular macros to that group as buttons.
First, add a custom group to an existing tab or your own tab. For the detailed instructions, please see:
- How to create a custom ribbon tab
- How to add a custom group
And then, add a macro button to your custom group by performing these steps:
- Right-click the ribbon, and then click Customize the Ribbon .
- In the list tabs on the right, select your custom group.
- In the Choose commands from list on the left, select Macros .
- In the list of macros, choose the one you wish to add to the group.
- Click the Add button.
- Click OK to save your changes and close the main dialog box.
How to run a macro on opening a workbook
Sometimes you may want to run a macro automatically on opening a workbook, for example, to display some message, run script or clear a certain range. This can be done in two ways.
Run macro automatically by using Workbook_Open event
Below are the steps to create a macro that automatically runs whenever you open a specific workbook:
- Open the workbook in which you want the macro to be executed.
- Press Alt + F11 to open the Visual Basic Editor.
- In the Project Explorer, double click ThisWorkbook to open its Code window.
- In the Object list above the Code window, select Workbook . This creates an empty procedure for the Open event to which you can add your own code like shown in the screenshot below.
For example, the following code will display a welcome message each time the workbook is opened:
Trigger macro on workbook opening with Auto_Open event
Another way to run a macro automatically on workbook opening is by using the Auto_Open event. Unlike the Workbook_Open event, Auto_Open() should sit in a standard code module, not in ThisWorkbook .
Here are the steps to create such a macro:
- In the Project Explorer , right-click Modules , and then click Insert > Module .
- In the Code window, write the following code:
Here's an example of the real-life code that displays a message box on workbook opening:
Note! The Auto_Open event is deprecated and available for backwards compatibility. In most cases, it can be replaced with the Workbook_Open event. For more information, please see Workbook_Open vs. Auto_Open .
Now that you know lots of ways to run a macro in Excel, you just need to choose the one best suited for your needs. I thank you for reading and hope to see you on our blog next week!
You may also be interested in
- Excel macro tutorial for beginners
- How to record a macro in Excel
- How to insert VBA code
- How to enable macros in Excel
- Personal Macro Workbook in Excel
- User-defined functions vs VBA macros: pros and cons
Table of contents
13 comments
I have created some macro buttons on Excel Quick access toolbar. But if I move my Macro.xlam to other folder (change path), although I have already loaded Macro.xlam (by Developer/Excel Add-in), the macro buttons could not run. How could I do that only load the Macro.xlam and run the macro buttons, not depend on the file location? Thank you.
Hi! In order for Excel to find and automatically load your XLAM file, you must tell Excel where the file is located. If you have moved the file to another location, load it again by using the Developer menu.
I wrote a macro to hide certain rows and columns, print a specific selection and then unhide the pertinent columns and save the workbook. All this is attached to a button. When it prints I get blank pages
Range("b1:F117").Select Selection.PrintOut Copies:=1, Collate:=True
Any suggestion regarding what I am doing wrong?
Your request goes beyond the advice we provide on this blog. If you have a specific question about the operation of a function or formula, I will try to answer it.
Is it possible to assign a macro button to a toolbar that can then be opened on any pc? It seems that when I forward my document with the macro buttons added to the toolbar, they disappear when the new user opens the document however the functions are still seen as listed macros in the document.
Please help
Hi! With a usual Excel file, you cannot transfer your toolbar settings to another user.
I need to run a macro to export a document to PDF, but I need to save to a different location each time I run it. Is this a possibility? I would ideally like to link this macro to a button.
Hello! To store the macros you use frequently, I recommend the Personal Macro Workbook. For more information, please visit: Personal Macro Workbook in Excel - make macros available in all workbooks .
How do I format the TEXT in a macro button, say BOLD, or FONT 14,,,etc
Right click/ edit text/ then just make the changes you want
Hi Svetlana!
Thank you for this useful post. I created a macro spreadsheet with keyboard shortcuts. I would like to ask if it is possible to convert those shortcuts into buttons in ribbon? And then have those shortcuts removed? ( I want to share the sheet with others, but don't want them to accidentally press those keys)
Yes, I found :)
Good Afternoon Svetlana,
I am trying to write two separate if statements with a nested LOOKUP so the responses in the relating cells don't return with a column heading nor an #N/A reply.
The first LOOKUP statement is as follows: =LOOKUP(2,1/($O$6:$O$19=Q2),$M$6:$M$19)
If the response is an amount, it lists the last amount, but if there isn't an amount -or the range is blank, I want it to reply with 0.00
The second LOOKUP statement is as follows: =LOOKUP(2,1/(K:K""),K:K)
The response is a date, but if there has not been a payment in the affecting range, I want it to reply with "No Payment Received"
Please help. Regards, Roger
Post a comment
Excel Tutorial: How To Create A Macro Button In Excel
Introduction.
If you've ever found yourself repeatedly performing the same tasks in Excel, then creating a macro button could be a game-changer for you. A macro button is a customizable button that automates a series of commands or actions in Excel, saving you time and effort. In this tutorial, we'll show you how to create your own macro button and the importance of using them in Excel to streamline your workflow and increase productivity.
Key Takeaways
- Macro buttons in Excel automate repetitive tasks, saving time and effort.
- Using macro buttons can streamline workflow and increase productivity in Excel.
- Macros in Excel can be customized and assigned to shortcut keys for easy access.
- Customizing the Excel ribbon and modifying the appearance of macro buttons can enhance user experience.
- Practicing creating and using macro buttons is encouraged for increased efficiency in Excel.
Understanding Macros in Excel
When working with Excel, macros can be a powerful tool to automate repetitive tasks and streamline workflows. Understanding the basics of macros is essential for anyone looking to increase efficiency in Excel.
A macro in Excel is a set of instructions that can be recorded and saved for later use. It allows users to automate tasks by recording a sequence of commands and executing them with the click of a button.
Macros offer several benefits to Excel users, including:
- Automation of repetitive tasks
- Increased productivity and efficiency
- Consistency in task execution
- Error reduction
- Customization of Excel functionality
By using macros, Excel users can save time and increase efficiency in various ways:
1. Automating repetitive tasks
Tasks that are performed frequently, such as formatting data, can be automated with macros, saving users time and effort.
2. Streamlining complex processes
Macros allow users to automate complex processes, such as data analysis or report generation, reducing the time required for these tasks.
3. Simplifying data manipulation
With macros, users can create custom functions to manipulate data, making it easier to perform specific actions on their datasets.
Overall, understanding macros in Excel is crucial for anyone looking to optimize their workflow and make the most out of the software's capabilities.
Creating a Macro in Excel
Macros can be a powerful tool in Excel, allowing you to automate repetitive tasks and save time. In this tutorial, we will walk through the process of creating a macro button in Excel.
Recording a macro in Excel is a straightforward process that allows you to capture a series of actions and then replay them with the click of a button. Here's how to do it:
- Step 1: Open the Excel workbook in which you want to create the macro.
- Step 2: Navigate to the "View" tab on the Excel ribbon and click on the "Macros" button.
- Step 3: Select "Record Macro" from the dropdown menu. A dialog box will appear, allowing you to name the macro and choose where to store it.
- Step 4: Once you've named and located the macro, click "OK" to start recording your actions. Excel will now track everything you do.
- Step 5: After completing the series of actions you want to capture, click on the "Stop Recording" button in the same "Macros" dropdown menu.
After recording the macro, it's important to give it a descriptive name and consider assigning a shortcut key for quick access.
- Naming the macro: Choose a name that reflects the purpose of the macro and makes it easy to identify in the list of macros.
- Assigning a shortcut key: To assign a shortcut key to the macro, go back to the "Macros" dropdown menu, select "View Macros," and then choose the macro you want to assign a shortcut key to. Click on the "Options" button, and you can specify a letter or number to act as the shortcut key.
Adding a Macro Button to the Excel Ribbon
Adding a macro button to the Excel ribbon can greatly improve the efficiency of performing repetitive tasks. Here's how you can do it:
Step 1: Open the Excel Options
To begin customizing the ribbon, click on the "File" tab on the Excel ribbon and then select "Options" from the menu on the left-hand side.
Step 2: Customize the Ribbon
Once the Excel Options window opens, select "Customize Ribbon" from the left-hand side. This will allow you to add a new tab or group to the ribbon.
Step 1: Access the Developer Tab
If the Developer tab is not already visible on the ribbon, you will need to enable it. To do this, go to the "File" tab, choose "Options," select "Customize Ribbon," and then check the "Developer" option in the right-hand column.
Step 2: Open the Visual Basic for Applications (VBA) Editor
Go to the Developer tab and click on "Visual Basic" to open the VBA Editor. Here, you can write and edit macros that will be assigned to the button.
Step 3: Insert a Form Control Button
Return to the Excel worksheet, click on the Developer tab, and then select "Insert" in the Controls group. Choose the "Button" form control and draw the button on the worksheet where you want it to appear.
Step 4: Assign the Macro to the Button
Right-click on the button, select "Assign Macro," choose the macro you want to assign to the button, and then click "OK."
Assigning the Macro to the Button
Once you have created your macro, it’s time to assign it to a button to make it easily accessible to users. Follow these steps to assign the macro to the button:
Modifying the Macro Button Properties
Once you have created a macro button in Excel, you may want to customize its appearance and add additional information to make it more user-friendly and informative.
If you want to change the appearance or text of the macro button, you can do so by right-clicking on the button and selecting 'Edit Text' or 'Edit Button Text'. This will allow you to change the text displayed on the button to make it more descriptive or relevant to the action it performs. You can also modify the formatting, such as font size, color, and style, to make the button stand out on the worksheet.
An effective way to provide additional information about the macro button is by adding a tooltip. To add a tooltip, right-click on the button and select 'Assign Macro'. In the 'Assign Macro' dialog box, click on 'Options' and enter the tooltip text in the 'Description' field. This tooltip will appear when the user hovers their cursor over the macro button, providing them with a brief explanation of its function.
Overall, macro buttons provide a convenient way to automate repetitive tasks and increase efficiency in Excel. By creating and using macro buttons, users can save time and effort, allowing them to focus on more complex analytical work. We encourage you to practice creating and using macro buttons to experience the benefits firsthand and take your Excel skills to the next level. With dedication and practice, you'll soon be amazed at how much more productive you can be with the help of macro buttons.
Immediate Download
MAC & PC Compatible
Free Email Support
Related aticles
Excel Tutorial: What Does #### Mean In Excel
Understanding Mathematical Functions: How To Call A Function In Vba
Understanding Mathematical Functions: How To Add Function In Google Sheets
Understanding Mathematical Functions: How To Fill In A Table Using A Function Rule
Understanding Mathematical Functions: What Are The Basic Functions Of A Cell
Making Write 15 Minutes On A Timesheet
Making Identify Sheet Sizes
Mastering Formulas In Excel: What Is The Formula For Standard Deviation
Mastering Formulas In Excel: What Is The Formula Of Force
Mastering Formulas In Excel: What Is Net Present Value Formula
Mastering Formulas In Excel: How To Write Formula In Google Docs
Mastering Formulas In Excel: How To Do A Formula In Google Sheets
- Choosing a selection results in a full page refresh.
How to Permanently Add Macros in Excel Ribbon?
Having macros that automate custom tasks in Excel can be huge time savers. Like any tool, it would be ideal to have these macros easily accessible so we can run them any time we need them.
We often add buttons (like radio buttons ) in specific worksheets to run our macros. However, finding the button to run the macro would be too burdensome if you have loads of worksheets in your workbook.
This is why setting up our macro button in the Excel Ribbon is a good idea. Whatever sheet we may be on, we can always run the macro with just a few clicks.
Also, if you have macros that you want to be available in other workbooks as well, you can do so when you add them to the Excel Ribbon.
Steps to Permanently Add Macros in Excel Ribbon
Step #1: Add the Macros to the Personal Macro Workbook
A Personal Macro Workbook is a special workbook that runs in the background whenever we open the Excel app.
We will use this workbook to store the macros that we have created. This way, the macros are still accessible even when we open a different Excel file.
To create a personal macro workbook:
1. Go to the Developer tab and click the Record Macro button.
2. The Record Macro menu will appear.
Click the “Store macro in” dropdown list. Select Personal Macro Workbook. After that, click OK.
Excel will then proceed with recording whatever you do in the file and create its corresponding macro.
I’m assuming that you already have your macros ready, so all you need to do next is stop the recording by clicking the Stop Record button (just below the first worksheet).
(However, if you need to record a macro, please proceed with performing the steps you wish to be automated in the workbook and then click on the stop button once done.)
3. Once you click the Stop Recording button, the Personal Macro Workbook should now be created.
To verify this, press ALT + F11 to open the VBA Editor .
On the list of projects, you should see PERSONAL.XLSB .
If you did some steps while the Record Macro is turned on, you should see the macro recorded on Module1 of this file.
4. Highlight the entire code in this module and delete it (if needed).
Then, add the code of your macros there.
That’s it! Your Personal Macro Workbook should now all be set up.
Step #2: Customize the Excel Ribbon
Now, it’s time to add the button(s) in the Excel Ribbon.
(Note: If you prefer adding a button in the Quick Access Toolbar, you can proceed to the next section of this article.)
1. Right-click anywhere on the Excel Ribbon.
2. A pop-up menu will appear. Select Customize the Ribbon .
3. In the Excel Options menu, click the New Tab button.
The new tab will then be inserted in the list of Main Tabs.
4. Right-click on the new tab and select Rename .
5. Enter your desired tab name.
In my example above, I’ve named it “Macros”.
6. Next, let’s rename the group under the current tab.
Right-click on New Group (Custom) and select Rename.
Type the appropriate name for the group.
In my example above, I’ve named the group “Worksheet” because the macros that I will add to it are worksheet-related functions.
If you have multiple macros to add, you can add more groups so you can group related macros.
To do this, just click the New Group button at the bottom.
The new group will then be added to the current tab.
Don’t forget to give it a new name by right-clicking on it and selecting Rename .
7. After adding all the groups, it’s now time to add the macro buttons.
- Click the first group you would like to add buttons to.
- Click the “ Choose commands from” dropdown list. From the list of options, select Macros .
- You should see the macros added in the PERSONALXLSB file. Select the first macro you would like to add to the group.
- Once selected, click the Add >> button.
Repeat these steps until you’ve added all macros to their appropriate group.
8. Next, let’s rename each of the macros to specify how they will appear on the buttons.
- Right-click on the macro and select Rename .
- Choose the symbol or the image that you’d like to appear on the button.
- Edit the Display Name .
- Once done, click OK.
Repeat these steps until you’ve renamed all your macros and added their corresponding symbols.
9. You can re-arrange the order of the macros, the groups, and the tabs.
You just need to select the object (whether it’s the macro, group, or tab) and click the up or down arrow on the right side.
10. If you’re happy with the names and the arrangement of the tab, click the OK button.
And that’s it! You have successfully added your macro(s) to the Excel ribbon.
You can click on these macro buttons to test them.
Also, try closing the current file. Then open a new one and see if you still have the macro buttons visible in the Excel Ribbon.
You can also add a custom autofill list after creating your customized Excel ribbon.
ADDITIONAL TIPS
- If you must update your macros and need to temporarily disable or hide the buttons from the Excel Ribbon, just uncheck the corresponding tab from the Customize Ribbon menu (as shown below).
- To permanently remove them from the Excel Ribbon, right-click on the corresponding tab and select Remove.
- To reset all the changes made to the Excel Ribbon, just click Reset >> Reset All Customizations .
Permanently Add Macros in the Quick Access Toolbar
Aside from the Excel Ribbon, you can also permanently add the macros in the Quick Access Toolbar (QAT).
By default, the QAT is located just above the Excel Ribbon.
With the QAT, you can add the macros as icons on top. Users can readily access them without browsing through the tabs. You won’t, however, see the names of each function unless you hover the mouse over each icon.
To add your macros to this toolbar, do the following:
1. Add the macros to the Personal Macro Workbook (same steps as above).
2. Click the Customize Quick Access Toolbar button (see the last icon in the QAT).
A pop-up menu will show up. Select More Commands .
3. You’ll then be redirected to the Excel Options for Quick Access Toolbar.
- In the “ Choose commands from” dropdown list, select Macros .
- From the list of macros that appear, select the macro you want to add to the QAT.
- Click the Add >> button.
- You should now see your selected macro in the list of QAT functions.
4. Next, we’ll modify the macro to change its display name and icon.
- Select the macro from the list of QAT commands.
- Click the Modify button.
- Select the icon that you’d like to appear in the QAT.
- Edit the display name.
5. You should now see the updated name and icon for your macro. If you’re happy with it, click OK.
6. That’s it! You should now see your macro added to the QAT.
If you want your macros to be accessible from any Excel workbook that you open, you need to add them to the Excel Ribbon (or the Quick Access Toolbar) as described in the steps above. Remember to add the macros to the Personal Macro Workbook – this is the only way the macros can run from any Excel file on your computer.
Leave a Comment Cancel reply
Save my name, email, and website in this browser for the next time I comment.
- Search forums
- Board Rules
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
- If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
- Question Forums
- Excel Questions
Macro to add button to ribbon?
- Thread starter TattYY
- Start date Apr 7, 2020
- Apr 7, 2020
Hi I have created a macro that needs to be used by multiple people, most of which have quite a limited knowledge of excel so i'm trying to come up with a really simple way of sharing it. It occurred to me i can turn the macro into an Add-In, then create another macro that installs the add-in and assign it to a nice big button, all they have to do it click on it and it's done. Super easy so far... The next part is then putting that macro on ribbon somewhere so it is easy to use. I was hoping i could simply record another macro to do this, but for some reason it doesn't record anything. I have a hunch it just can't be done, but really hoping someone can tell me it can, or can point me in another direction to automate this step? Thanks
Excel Facts
Yinkajewole, active member.
- Apr 8, 2020
you may have to use a CustomUI to add your macro to the ribbon. Learn from here
Similar threads
- Feb 3, 2024
- Jan 4, 2024
- Apr 10, 2023
- umarpervaiz425
- Nov 27, 2023
- Nov 21, 2023
Forum statistics
Share this page.
We've detected that you are using an adblocker.
Which adblocker are you using.
Disable AdBlock
Disable AdBlock Plus
Disable uBlock Origin
Disable uBlock
Create a form in Word that users can complete or print
In Word, you can create a form that others can fill out and save or print. To do this, you will start with baseline content in a document, potentially via a form template. Then you can add content controls for elements such as check boxes, text boxes, date pickers, and drop-down lists. Optionally, these content controls can be linked to database information. Following are the recommended action steps in sequence.
Show the Developer tab
In Word, be sure you have the Developer tab displayed in the ribbon. (See how here: Show the developer tab .)
Open a template or a blank document on which to base the form
You can start with a template or just start from scratch with a blank document.
Start with a form template
Go to File > New .
In the Search for online templates field, type Forms or the kind of form you want. Then press Enter .
In the displayed results, right-click any item, then select Create.
Start with a blank document
Select Blank document .
Add content to the form
Go to the Developer tab Controls section where you can choose controls to add to your document or form. Hover over any icon therein to see what control type it represents. The various control types are described below. You can set properties on a control once it has been inserted.
To delete a content control, right-click it, then select Remove content control in the pop-up menu.
Note: You can print a form that was created via content controls. However, the boxes around the content controls will not print.
Insert a text control
The rich text content control enables users to format text (e.g., bold, italic) and type multiple paragraphs. To limit these capabilities, use the plain text content control .
Click or tap where you want to insert the control.
To learn about setting specific properties on these controls, see Set or change properties for content controls .
Insert a picture control
A picture control is most often used for templates, but you can also add a picture control to a form.
Insert a building block control
Use a building block control when you want users to choose a specific block of text. These are helpful when you need to add different boilerplate text depending on the document's specific purpose. You can create rich text content controls for each version of the boilerplate text, and then use a building block control as the container for the rich text content controls.
Select Developer and content controls for the building block.
Insert a combo box or a drop-down list
In a combo box, users can select from a list of choices that you provide or they can type in their own information. In a drop-down list, users can only select from the list of choices.
Select the content control, and then select Properties .
To create a list of choices, select Add under Drop-Down List Properties .
Type a choice in Display Name , such as Yes , No , or Maybe .
Repeat this step until all of the choices are in the drop-down list.
Fill in any other properties that you want.
Note: If you select the Contents cannot be edited check box, users won’t be able to click a choice.
Insert a date picker
Click or tap where you want to insert the date picker control.
Insert a check box
Click or tap where you want to insert the check box control.
Use the legacy form controls
Legacy form controls are for compatibility with older versions of Word and consist of legacy form and Active X controls.
Click or tap where you want to insert a legacy control.
Select the Legacy Form control or Active X Control that you want to include.
Set or change properties for content controls
Each content control has properties that you can set or change. For example, the Date Picker control offers options for the format you want to use to display the date.
Select the content control that you want to change.
Go to Developer > Properties .
Change the properties that you want.
Add protection to a form
If you want to limit how much others can edit or format a form, use the Restrict Editing command:
Open the form that you want to lock or protect.
Select Developer > Restrict Editing .
After selecting restrictions, select Yes, Start Enforcing Protection .
Advanced Tip:
If you want to protect only parts of the document, separate the document into sections and only protect the sections you want.
To do this, choose Select Sections in the Restrict Editing panel. For more info on sections, see Insert a section break .
If the developer tab isn't displayed in the ribbon, see Show the Developer tab .
Open a template or use a blank document
To create a form in Word that others can fill out, start with a template or document and add content controls. Content controls include things like check boxes, text boxes, and drop-down lists. If you’re familiar with databases, these content controls can even be linked to data.
Go to File > New from Template .
In Search, type form .
Double-click the template you want to use.
Select File > Save As , and pick a location to save the form.
In Save As , type a file name and then select Save .
Start with a blank document
Go to File > New Document .
Go to File > Save As .
Go to Developer , and then choose the controls that you want to add to the document or form. To remove a content control, select the control and press Delete. You can set Options on controls once inserted. From Options, you can add entry and exit macros to run when users interact with the controls, as well as list items for combo boxes, .
Adding content controls to your form
In the document, click or tap where you want to add a content control.
On Developer , select Text Box , Check Box , or Combo Box .
To set specific properties for the control, select Options , and set .
Repeat steps 1 through 3 for each control that you want to add.
Set options
Options let you set common settings, as well as control specific settings. Select a control and then select Options to set up or make changes.
Set common properties.
Select Macro to Run on lets you choose a recorded or custom macro to run on Entry or Exit from the field.
Bookmark Set a unique name or bookmark for each control.
Calculate on exit This forces Word to run or refresh any calculations, such as total price when the user exits the field.
Add Help Text Give hints or instructions for each field.
OK Saves settings and exits the panel.
Cancel Forgets changes and exits the panel.
Set specific properties for a Text box
Type Select form Regular text, Number, Date, Current Date, Current Time, or Calculation.
Default text sets optional instructional text that's displayed in the text box before the user types in the field. Set Text box enabled to allow the user to enter text into the field.
Maximum length sets the length of text that a user can enter. The default is Unlimited .
Text format can set whether text automatically formats to Uppercase , Lowercase , First capital, or Title case .
Text box enabled Lets the user enter text into a field. If there is default text, user text replaces it.
Set specific properties for a Check box .
Default Value Choose between Not checked or checked as default.
Checkbox size Set a size Exactly or Auto to change size as needed.
Check box enabled Lets the user check or clear the text box.
Set specific properties for a Combo box
Drop-down item Type in strings for the list box items. Press + or Enter to add an item to the list.
Items in drop-down list Shows your current list. Select an item and use the up or down arrows to change the order, Press - to remove a selected item.
Drop-down enabled Lets the user open the combo box and make selections.
Protect the form
Go to Developer > Protect Form .
Note: To unprotect the form and continue editing, select Protect Form again.
Save and close the form.
Test the form (optional)
If you want, you can test the form before you distribute it.
Protect the form.
Reopen the form, fill it out as the user would, and then save a copy.
Creating fillable forms isn’t available in Word for the web.
You can create the form with the desktop version of Word with the instructions in Create a fillable form .
When you save the document and reopen it in Word for the web, you’ll see the changes you made.
Need more help?
Want more options.
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Microsoft 365 subscription benefits
Microsoft 365 training
Microsoft security
Accessibility center
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
Ask the Microsoft Community
Microsoft Tech Community
Windows Insiders
Microsoft 365 Insiders
Was this information helpful?
Thank you for your feedback.
IMAGES
VIDEO
COMMENTS
Adding Macro Buttons to the Excel Ribbon To add a button to the ribbon, start by right-clicking anywhere on the ribbon or ribbon tabs. Then select Customize the Ribbon. This will open the Excel Options page, and Customize Ribbon should already be highlighted on the left-hand side.
00:26 Getting Started 02:02 Test the Macros 01:36 Add Macro to Drop Down List 04:53 Change a Macro Button 06:29 Test the Revised Button 06:47 Copy Your Macro Code 08:50 Find a Button Image 11:51 Save As an Add-In 13:22 Get the Sample File Open the Sample File
Click File > Options in the list on the left-hand border In the Options dialog select Customize Ribbon > Customize the Ribbon > Main Tabs and add a check-mark in the box for "Developer", and click OK. Add a Macro Button In Excel, select the Developer tab, then click on the " Insert " dropdown in the Controls section.
Click File > Options > Quick Access Toolbar. In the Choose commands from list, click Macros. Select the macro you want to assign a button to. Click Add to move the macro to the list of buttons on the Quick Access Toolbar. To replace the default macro icon with a different button for your macro, click Modify.
Windows macOS Macros and VBA tools can be found on the Developer tab, which is hidden by default. The first step is to enable it. For more information, see the article: Show the Developer tab. Add a button (Form control) On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Button .
It is only possible to add buttons to the main ribbon using dynamic XML when using VBA. See below for more information. ' Constants Private Const COMMANDBAR_NAME As String = "Custom Toolbar" Private Const BUTTON_CAPTION As String = "My Button"' Open Private Sub Workbook_Open() ' Variables Dim objCommandBar As CommandBar
Learn how to add custom macro buttons to the Excel Ribbon or Quick Access Toolbar. ...more ...more 10K views 907K views Sign up for our Excel webinar, times added weekly:...
1. Create a new tab on the ribbon Excel allows you to customize the ribbon and link your personal macros to a button each. So you can quickly and easily access your favorite macros. Go to "File" on the ribbon. Press with left mouse button on "Options". Press with left mouse button on "Customize Ribbon".
Tip. If the Developer tab is not added to your Excel ribbon, press Alt + F8 to open the Macro dialog. Run a macro with custom keyboard shortcut If you execute a certain macro on a regular basis, you can assign a shortcut key to it. A shortcut can be added while recording a new macro and to an existing one. For this, carry out these steps:
Introduction Have you ever found yourself performing the same repetitive tasks in Excel? If so, you may benefit from incorporating macro buttons into your spreadsheets. A macro button is a clickable object that, when pressed, executes a series of pre-recorded commands.
To begin customizing the ribbon, you'll need to open the Visual Basic for Applications (VBA) editor in Excel. Here's how to do it: Step 1: Open Excel and navigate to the "Developer" tab on the ribbon. Step 2: Click on the "Visual Basic" button in the "Code" group. This will open the VBA editor window. B. Adding a new module for the custom ribbon.
Adding a Form Control Button. In the Ribbon, select Developer > Insert > Form Controls > Button. Click and drag in the worksheet to create a button. As soon as you release the mouse button, the assign macro dialog box will appear. Scroll down to 1) select the macro you wish to assign to the button, and then, 2) click OK.
Step 1: Open the Excel workbook in which you want to create the macro. Step 2: Navigate to the "View" tab on the Excel ribbon and click on the "Macros" button. Step 3: Select "Record Macro" from the dropdown menu. A dialog box will appear, allowing you to name the macro and choose where to store it.
1. Go to the Developer tab and click the Record Macro button. 2. The Record Macro menu will appear. Click the "Store macro in" dropdown list. Select Personal Macro Workbook. After that, click OK. Excel will then proceed with recording whatever you do in the file and create its corresponding macro.
Make a custom macro button on the Ribbon Menu in Excel.Excel Quickie 39: https://www.youtube.com/watch?v=KpqKmH3a-osHow to Make a Personal Macro Workbook: ht...
Steps: Click as follows: Developer > Insert > ActiveX Controls > Button. Like the previous section, set the button size using the mouse. Next, right-click on the button and select View Code from the context menu. By using the other menu you can edit the macro button as you require.
How to add a macro button to the ribbon in Excel. - YouTube If you have macros in your Personal Workbook or as Addins that you run often on for example workbooks or worksheets that...
The above code creates a temporary new ribbon, which would normally vanish if you were to close then re-open Excel. By putting this in the Add-in and naming the sub Aut pen it creates the toolbar every times you start Excel. Bit of a dirty way to do it, but it does the job in a simple way and requires no messing about with XML or CustomUI
Now I'll show you how to add the same macro to the Quick Access Toolbar, so it'll be up here next to Redo. Click FILE and Options, and this time, click Quick Access Toolbar. Again, I choose commands from Macros, and here's the macro in my personal workbook. Click Add. To rename it, click Modify. Call it "Fill Days" and choose the same grid icon.
1 Answer Sorted by: 0 You can use CommandBars and CommandBarControls classes to accomplish your work. Please note some considerations: When adding a control, you should specify the Temporary property as True or False If you don't remove your controls before adding more, and they're the same, they will be duplicated.
Step 3: Edit .rels File and Add Relationship to Excel File. To customize the ribbon in Excel VBA, we will modify the .rels file to add a relationship with the Custom file to the sample Excel file. First, copy the _rels folder from the zip file. Next, paste it into any folder outside the zip file.
Public Sub AddHighlightRibbon () Dim ribbonXml As String ribbonXml = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">" ribbonXml = ribbonXml + " <mso:ribbon>" ribbonXml = ribbonXml + " <mso:qat/>" ribbonXml = ribbonXml + " <mso:tabs>" ribbonXml = ribbonXml + " <mso:tab id=""highlightTab"" label=""Highlight"" in...
If the developer tab isn't displayed in the ribbon, see Show the Developer tab. Open a template or use a blank document. To create a form in Word that others can fill out, start with a template or document and add content controls. Content controls include things like check boxes, text boxes, and drop-down lists.