Pinterest Stumbleupon Whatsapp
Advertisement

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

A well-made Excel macro 5 Resources for Excel Macros to Automate Your Spreadsheets 5 Resources for Excel Macros to Automate Your Spreadsheets Searching for Excel macros? Here are five sites that have got what you're looking for. Read More 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 How to Send Emails From an Excel Spreadsheet Using VBA Scripts How to Send Emails From an Excel Spreadsheet Using VBA Scripts We'll show you how to set up automated emails from within Excel using Collaboration Data Objects, (CDO) and VBA scripts. Our code templates make this a lot easier than it sounds! Read More  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.

customize the ribbon excel

On the next screen, click New Tab.

excel new ribbon tab

Highlight your new tab, then click Rename. I’m going to call mine Macros, but you can choose whatever you want.

rename excel ribbon tab

Press OK to return to the spreadsheet.

excel macros tab

Our Macros tab has appeared, just as we were hoping. However, it’s a little bit empty at the moment.

excel empty macros tab

To give it some functionality, let’s whip up a few basic macros The 10 Best OneNote Macros You Should Try The 10 Best OneNote Macros You Should Try OneNote is an awesome 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 now. Read More .

Creating 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.

excel developer macros

Enter the macro name dateandtime and then click Create.

excel create macro

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 10 Easy Excel Timesavers You Might Have Forgotten 10 Easy Excel Timesavers You Might Have Forgotten These ten tips are sure to minimize your time performing menial Excel tasks and boost your spreadsheet productivity. Read More 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.

excel run macro

You should see something like this:

excel message box

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.

excel customer headers

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 9 Tips for Formatting an Excel Chart in Microsoft Office 9 Tips for Formatting an Excel Chart in Microsoft Office First impressions matter. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know about making your charts attractive and engaging in Excel 2016. Read More 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.

excel autoheader

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.

excel linked spreadsheet

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.

excel choose commands from

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.

excel add macro

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

excel rename macro

Now we have a fully functional toolbar that offers instant access to those macros we created.

excel finished toolbar

Make a Toolbar That Works for You!

Everyone knows that Excel is an incredibly powerful piece of software 16 Excel Formulas that Will Help You Solve Real Life Problems 16 Excel Formulas that Will Help You Solve Real Life Problems The right tool is half the work. Excel can solve calculations and process data faster than you can find your calculator. We show you key Excel formulas and demonstrate how to use them. Read More .

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 The Excel VBA Programming Tutorial for Beginners The Excel VBA Programming Tutorial for Beginners VBA is a Microsoft Office power tool. You can use it to automate tasks with macros, set triggers, and lots more. We'll introduce you to Excel visual basic 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’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

Leave a Reply

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

  1. Doc
    July 27, 2017 at 2:38 pm

    Not only is enabling VBA macros a great way to get infected with a virus, anyone who uses Excel for *everything* is using it as a hammer because every problem looks like a nail.