Join Dennis Taylor for an in-depth discussion in this video Running macros from an object or clip art, part of Excel 2010: Macros.
If a macro is designed to be used by others, or is designed to work only in specific worksheets, you might want to consider activating them by clicking a button. And that's the case in this workbook right here called Phone List. When we put the mouse right on Sort by Name, you see the pointer there. It's going to actually sort these by last name, too. Notice it does that. At another time you might want to sort this list by department. The macros associated with the Sorting buttons are actually in this workbook, and we can create buttons.
We can use clip art and make them be the starting point for macros. Let's assume that people using this don't know how to sort data in Excel. But this makes it simple and easy for them. So how do we set this up? What I'm going to do is hold down the Ctrl key and simply click one of these, for example Sort by Name, and then delete it, and show how we can create a button like this. So I want a button right here, just select the one we saw. Quick look here on the View tab first though to remind us--because I did say it and didn't show it to you-- the View tab, Macros button by Viewing Macros, we can see the macros in this particular workbook.
There is a macro called SortByDepartment. There is macro called SortByName. They are in this workbook. We would like a button here in column A. Where do we go? Insert tab > Shapes. Use any shape you want. Sometimes you'll see these maybe done whimsically with a smiley face. There is a heart shape. There all called kinds of buttons, explosion-type buttons, 32- 16-point stars, all kinds of stuff. I'll be a little conservative here and just use rounded rectangle.
Draw it. If you want to look so-so, hold down the Alt key, drag these edges. They line up perfectly with the cell boundaries. Not critical, but some people love that idea. Hold down Alt to do that. We can typewrite on it from the beginning "Sort by Name." And let's get the cosmetics out of the way, just click the corner, maybe go back to the Home tab, make it Bold, change the font if you wish, use centering, top-down, left-right, whatever. Redesign that, change the color of it, and so on, and so on, and so on.
The key step of course is to right- click on this and do this near the edge, and you'll see a choice called Assign Macro. You're assigning a macro to this button. And which one? Here are the available macros, the ones in this workbook only; we could narrow it that way. We want SortByName; that's the macro that will sort these by last name. Click OK, and we've made the association. If we're done with all the cosmetics and everything, click outside of it.
We'll click this button. It will run our macro to sort this by last name. Now you can do this with clip art, too. Just a quick example here. I'm going to go to the Insert tab > Clip Art. Recently I used something like this. I simply typed in "car". Click Go. I'd be hard-pressed to give you good strong reason for using this here, but nevertheless any object--whether it's a shape like what we saw or one of these-- could be a vehicle for running a macro. And here too, right-click > Assign Macro, and there it is. Maybe it's the SortByDepartment macro. Click OK.
Now until we click away from it-- maybe I want to make that smaller. Okay. Now again, a little bit frivolous here, I can't give you good reason for using that. But point here--and there is no pop-up that tells us what it's going to do-- but this will sort by department. And you saw how we did this with the objects. So here and there, particularly in workbooks where you have got macros designed only to manipulate data within that workbook, not a bad idea sometimes to use either a button or clip art to be the starting point for running the macros.
- Understanding what a macro does
- Recording and adding functionality to a macro
- Running macros
- Creating non-recordable VBA code
- Testing a macro in Step mode
- Joining two macros
- Using loop structures
- Streamlining macros
- Creating a Personal Macro Workbook