Ready to supercharge your Excel productivity? A custom toolbar can do just that.
A well-made Excel macro can perform a time-consuming task in one click, which is pretty convenient. It’s even more convenient when you construct a custom toolbar containing all your most commonly used macros.
Attaching a macro to a button is one solution, but there are times when it would be great to embed that functionality into the Excel interface. By following these steps, you can put your macros right there in the Ribbon.
Here’s how to get started on your custom toolbar.
Adding a Tab to the Ribbon
First, we’re going to add our own custom tab to the Ribbon which will house our toolbar. Right-click it and select Customize the Ribbon.
On the next screen, click New Tab.
Highlight your new tab, then click Rename. I’m going to call mine Macros, but you can choose whatever you want.
Press OK to return to the spreadsheet.
Our Macros tab has appeared, just as we were hoping. However, it’s a little bit empty at the moment.
To give it some functionality, let’s whip up a few basic macros.
First, we’re going to create a very, very simple macro to get the basics down.
Creating the Time and Date Macro
First, head to the Developer tab and click Macros.
Enter the macro name dateandtime 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, per the Now command. Save your work — remembering to save your spreadsheet as a macro-enabled file if you haven’t done so already — and head back to Excel.
Click Macros in the Developer tab once again, and this time highlight dateandtime and click Run.
You should see something like this:
Our message box macro works! Now let’s try something a little more complicated.
Creating the Auto-Header Macro
Open the Macros dialog once again, and this time enter the name customheaders and click Create.
Enter the following code under Sub customheaders():
Range("A1").Value = "Date" Range("B1").Value = "Title" Range("C1").Value = "Priority" Range("D1").Value = "Status" Range("E1").Value = "Finished?"
This will populate the cells in the brackets following the Range command with the corresponding text string. Of course, you can switch out the headers for whatever you want, and expand the list as necessary.
Add this line of code to the bottom:
Range("A1:E1").Font.Bold = True
This will apply bold formatting to each header. If you’ve added extra headers, make sure to adjust the cells in the brackets following the Range command.
Now, it’s time to head back to Excel and see whether our macro works as it should.
Indeed it did. This macro is handy if you’re 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 one click, rather than typing them out manually every time.
Now for one more macro.
Creating the Linked Spreadsheet Macro
First, create a brand new spreadsheet and save it. Then, open the document we were working with earlier and navigate to Developer > Macros. Enter the name linkedspreadsheet and click Create.
Enter the following code below Sub linkedspreadsheet():
Workbooks.Open ("C:\Users\bradj\Desktop\Make Use Of\VBA Toolbar\holiday availability.xlsx")
However, you’ll need to swap out the path for the spreadsheet you just created. To find that out, navigate to the document in File Explorer, right-click it and select Properties.
This macro opens the specified workbook. I’m using a holiday availability chart that I have to reference often while looking at another spreadsheet, so it makes sense for me to have a direct link to this file on my toolbar.
Save your work and head back to Excel to test it out.
It works great. When I run the macro, the linked spreadsheet opens up straight away. 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 the Ribbon. Select Customize the Ribbon.
Use the Choose commands 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.
Now we have a fully functional toolbar that offers instant access to those macros we created.
Make a Toolbar That Works for You!
Everyone knows that Excel is an incredibly powerful piece of software.
It can do just about anything — and as a result, the default set-up can be a little bit overwhelming.
Excel really comes into its own when you tailor it to your own usage. If you can create a custom toolbar composed of your own specialized macros, you’re sure to be more productive. No one knows your workflow better than you do, so no one is in a better position to shave off wasted time wherever possible.
All it takes is a little knowledge of VBA, and that’s an intimidating prospect if you’re not an experienced coder. However, it’s really not as difficult as it might seem, especially if you start with the VBA basics. Pretty soon, you’ll be ready to tailor your own macros, and that’s a great skill to have if you’re serious about mastering Excel.
Do you have a tip on creating Excel macros? Are you looking for help with the process of building a toolbar? Join the conversation in the comments section below!
Image Credit: ARTIST via Shutterstock.com