Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In a previous lesson we recorded two macros; one that we considered to be more useful and one that we considered to be less useful. Here in this workbook I've recreated the more useful version of that macro and now let's look at assigning that macro to your menus, toolbars and your own custom menus. It's a lot easier than you'd think. Earlier, we assigned shortcut keys to our macros, so they're already at our disposal without having to go through the Macro menu. We could simply insert a row, type in a title, highlight some space here, and that second macro we put the Control+T shortcut, so let's do that, and viola, our macro takes effect. Now I'm going to delete that information so that we can do it again. Let's insert a row and put in QUARTERLY SALES as our title. Only this time we're going up to the Tools > Customize menu and as long as this menu is open, we have all kinds of options. Let's go to the Commands and we'll scroll down through our categories, you'll notice that these commands match very closely the menu bar that you see up in the main body of the application. Well if we go all the way down, you'll see we have Macros, and more importantly, New Menu. Let's drag a New Menu item up to our menubar. Now, as long as you have the Customize dialog open, you can modify your new menu and you can modify your new menu items. So if we right-click on New menu, we have our menu name, which we're going to change to My Macros, and we'll go to Macros and we'll create a Custom Button which we'll drag in and drop in our new menu, and again the Customize dialog is open so I can right -click and change the name. Notice the ampersand sign. You're going to want to leave that alone. I'm going to change this to Title Format, and we have some other options here. You can change the image. You can change the image to something fun or useful. In this case, I don't really see an image that's going to be very effective for my title format, so I'm going back in to Edit Button Image and it brings up a small editor. I can clear the image that I'm starting with, and taking a colored brush, go in and paint myself an entirely new image. This is working on a pixel level, so everything you're drawing is very, very tiny, and you don't have that many cells to work with. This is a preview of what that is going to look like and I think the giant T will cover me, so I'll say OK. At least it's somewhat meaningful and when I close, that menu becomes active. I can select a range, go up to My Macros and choose Title Format, and now the first time I run this command, because I didn't specify it earlier, it's going to ask me which macro I want to assign to this button, and then in the future, it's going to refer back to that macro repeatedly. That's fine for macros recorded within a spreadsheet. Let's take a look at recording macros more globally so that you can use them with any worksheet that you open.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86607 Viewers
80 Video lessons · 135947 Viewers
59 Video lessons · 54689 Viewers
52 Video lessons · 68578 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.