One reason Excel users don’t record macros for a repetitive task is they have trouble figuring out which key combination to assign to the macro or how to create a toolbar icon for the macro. Those users waste precious time manually rerunning the macro or, worse, going through Excel’s menu system over and over.

Try this simple lesson to get your client’s users to record and use macros more often: Use the Drawing toolbar to create simple buttons for launching recorded macros. For example, suppose you need to filter a series of text entries from a variety of sources by copying the raw data into column A, going to Data | Filter | Advanced Filter, and copying the unique records to column C. Each time, you have to change the Advanced Filter dialog box’s settings.

On the other hand, you could record the process once in a macro like the one below.
Sub Macro1()
‘ Macro recorded by TechMails
    Range(“A:A”).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Range(“C1”), Unique:=True
End Sub

Then use the Drawing toolbar’s Oval, Rectangle, or AutoShapes tool to draw a temporary button on your worksheet:

  1. Right-click the drawing.
  2. Choose Assign Macro.
  3. Select the appropriate macro, as shown in Figure A.
  4. Click OK.
  5. Right-click the shape again.
  6. Choose Add Text.
  7. Type a label for your macro button.

Figure A

Remember: You can’t move this button by left-clicking it (because that launches the macro). Right-click the object, mouse over the object’s border, and then drag the object to a new location.

What do you think of this article format?

Our editors are developing and refining new methods and formats for delivering the solutions you need. Do shorter, step-by-step pieces such as this one help you overcome the IT problems that keep you up at night? Share your thoughts with our editors; you could win a free TechRepublic book or CD of your choice. Content for this article was derived from the TechRepublic Microsoft Office Suite e-newsletter. You can have tips like this delivered via e-mail each week by signing up here.