How to Build a Custom Excel Toolbar from VBA Macros

Ready to increase your Excel productivity? A custom toolbar can do that.

An Excel Macro Done Right 5 Excel Macro Resources to Automate Your Spreadsheets 5 Excel Macro Resources to Automate Your Spreadsheets Looking for Excel Macros? Here are five sites that have what you're looking for. Read More It's even more convenient when you build a custom toolbar that contains all the most used macros.

How to Attach a Macro to a Button How to Send Emails from an Excel Spreadsheet Using VBA Scripts How to Send Emails from an Excel Spreadsheet Using VBA Scripts scripts. Read More By following these steps, you can put your macros right there on the ribbon.

Here's how to get started with your custom toolbar.

Add a tab to the ribbon

First, we'll add our own custom tab to the Ribbon that will house our toolbar. Right click on it and select Customize the ribbon .

On the next screen, click on New tab .

Highlight your new tab, then click Rename . I'll call Macros mine, but you can choose what you want.

press OK to return to the spreadsheet.

Our Macros tab has appeared, just as we expected. However, it's a bit empty at the moment.

To give it some functionality, let's put together some basic macros. Top 10 OneNote Macros You Should Try Top 10 OneNote Macros You Should Try OneNote is an amazing productivity tool on its own, but you can take it to the next level using macros. We show you how to get started and the best macros available right now. Read more.

Creating Macros

First, let's create a very, very simple macro to get the basics right.

Creating the date and time macro

First, go to the Developer tab and click Macros .

Enter the name of the macro date and time and then click Create .

Excel will open the Visual Basic editor. Add the following code:

Sub dateandtime () MsgBox Now End Sub

This tells Excel to present a message box to the user and to populate that message box with the current time and date, based on the Now I send. Save your work – Remember to save your spreadsheet. 10 Easy Excel Time-Savers You Might Have Forgotten 10 Easy Excel Time-Savers You Might Have Forgotten. Read More

Click Macros in the Developer tab once more, and this time highlight date and time and click run .

You should see something like this:

Our macro message box works! Now let's try something a bit more complicated.

Creating the auto header macro

Open the Macros dialog once again, this time entering the name custom headers and click Create .

Enter the following code in Custom Sub Headers() :

Rango ("A1"). Valor = "Fecha" Rango ("B1"). Valor = "Título" Rango ("C1"). Valor = "Prioridad" Rango ("D1"). Valor = "Estado" Rango ( "E1"). Valor = "¿Terminado?"

This will fill the cells in parentheses after Distance command with the corresponding text string. You can of course change the headers to whatever you like and expand the list as needed.

Add this line of code at the bottom:

Rango ("A1: E1"). Font.Bold = True

This will apply bold formatting. 9 Tips for Formatting an Excel Chart in Microsoft Office. 9 Tips for Formatting an Excel Chart in Microsoft Office. First impressions are important. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know to make your charts beautiful and engaging in Excel 2016. Read More If you've added additional headings, be sure to wrap the cells in square brackets after Distance command.

Now it's time to go back to Excel and see if our macro works as it should.

In fact it did. This macro is useful if you are setting up new worksheets that contain new data every week or month. Once we add this macro to our toolbar, we'll be able to populate these headers with a single click, instead of manually typing them each time.

Now for one more macro.

Creating the linked spreadsheet macro

First, create a new spreadsheet and save it. Then open the document we were working with earlier and navigate to Developer> Macro . Enter the name link to spreadsheet and click Create .

Enter the following code below Sub linkedspreadsheet() :

Libros de trabajo. Abrir ("C: \ Users \ bradj \ Desktop \ Hacer uso de \ Barra de herramientas VBA \ disponibilidad de vacaciones.xlsx")

However, you will need to change the path of the spreadsheet you just created. To find out, navigate to the document in File Explorer, right-click it and select Properties .

This macro opens the specified workbook. I'm using a vacation availability table that I have to refer to often while viewing another spreadsheet, so it makes sense for me to have a direct link to this file in my toolbar.

Save your work and return to Excel to test it.

It works great. When I run the macro, the linked spreadsheet opens immediately. Now we just need to add all these macros to our toolbar.

Populating the toolbar

Open the Macros tab we created earlier and right-click on the ribbon. Select Customize the ribbon .

Use the Choose command from drop-down menu and select Macros .

You should see the three macros we made earlier. Highlight each one and use the Add button to insert it into a New Group under the Macros tab.

Highlight each macro and click Rename to give them a more presentable label, and add a custom icon.

We now have a fully functional toolbar that offers instant access to the macros we create.

Make a toolbar that works for you!

Everyone knows that Excel is incredibly powerful software. 16 Excel Formulas to Help You Solve Real-Life Problems. 16 Excel Formulas to Help You Solve Real-Life Problems. The right tool is half the job. Excel can solve calculations and process data faster than your calculator can. We show you the key Excel formulas and show you how to use them. Read more.

You can do just about anything, and as a result, the default settings can be a bit overwhelming.

Excel really comes into its own when you adapt it to your own use. If you can create a custom toolbar made up of your own specialized macros, you will surely be more productive. No one knows your workflow better than you do, so no one is in a better position to save wasted time wherever possible.

All you need is a little knowledge of VBA, and that's a daunting prospect if you're not an experienced programmer. However, it really isn't as difficult as it might seem, especially if you start with the basics of VBA. The Excel VBA Programming Tutorial for Beginners The Excel VBA Programming Tutorial for Beginners VBA is a powerful Microsoft Office tool. You can use it to automate tasks with macros, set triggers, and much more. We will introduce you to basic Excel visual programming with a simple project. Read more . Pretty soon, you'll be ready to tailor your own macros, and that's a great skill to have if you really want to master Excel.

Do you have any tips for creating Excel macros? Looking for help with the process of building a toolbar? Join the conversation in the comments section below!