5.14  Creating And Using Macros  

5.14.6 Assigning macros to buttons  

As talked above you can run any macro in your workbook from the macro dialog box, but this is not always the quickest method. If you have a macro such as Header_Info macro, which may be used frequently with a particular sheet, you can add a button to that sheet and assign the macro to the button. That way, you can click the button to run the macro, rather than using a menu command. You can still access the macro through the menu, if you desire to do so.

Creating a macro button: - Creating macro buttons is a simple activity. To do so, you have to simply draw a button and assign the macro to it. The button can be drawn by clicking the Create Button on the drawing toolbar and then dragging the pointer (in the shape of cross hairs) on your worksheet to draw the button. You can make the button as large or as small as you like, depending on the amount of space available on your sheet. You can resize or move the button later.

When you create the button, you also assign a macro to it from the list of macros in the workbook. After you have created the macro button assigned a macro to it, it is a good idea to give it a descriptive name so that you can remember what will happen when you click the button.

In this exercise, you will create and name the macro button, which will run the Header_Info macro.

1. Switch to the Sheet2 of the workbook. It has the headers created by Header_Info macro.

2. Click on the Drawing button. Drawing toolbar is now onscreen.

3. Click on AutoShapes. In the pull-menu select Basic shapes and further in this option choose Rectangle. Alternatively, you can also choose Rectangle directly from the Drawing toolbar. Either way, the pointer changes to a small pair of cross hairs, ready for you to draw a button.

4. Drag to draw a button to cover cells B3:B4.

5. Right click on the rectangle just drawn. In the pull down menu select Assign Macro…

6. Assign Macro dialog box appears. Select Header_Info.

7. Click on the OK button.

8. Again right click on the rectangle. In the drop down menu, now select Add Text.

9. Type the macro button text as Header Titles.

10. Next click outside the button. You will see that the newly created button with its text is ready to be put to use. See figure 14.24.

The button is named Header Titles. In case you type a bigger name only part of the same will appear.

Take a note that when you first create a macro button, you might not place it exactly where you want it or you might not size it correctly. If the button’s label is too long or if you decide that you need to move the button to a better location on your sheet, you can select it and resize or move it. Unlike most objects on a sheet, however, you cannot simply click the button to select it. If you simply click the button, you will run the macro. In order to select a macro button, you must press and hold down CTRL and then click the button.

You can also copy a macro button by holding down CTRL and then dragging the copy of the button to a new location. When you copy a button, it retains its connection to the macro, so you can place a copy on several sheets in a workbook and then run the macro from any of them.

Resizing A Macro Button

In this exercise, you will learn to resize the macro button in order to fit the size of the button label (if it is not fitting on the button).

1. Hold down CTRL and then click the macro button and then release CTRL. The macro button is selected and a border appears around it with small square handles at each side and corner. See figure 14.25. Make sure that you release CTRL after selecting the button; otherwise, you will run the macro assigned to that button.

2. Place your mouse pointer over the handle and pull outside or inside as per the requirement of resizing.

3. Click away from the button to de-select it. The button is resized to the new requirement.

Moving A Macro Button

In this exercise, you will learn to move the macro button to a new location.

1. Hold down CTRL and then click the macro button and then release CTRL.
   The macro button is selected and a border appears around it. Be sure that you release CTRL after selecting the button.

2. Drag the button by a border (not a handle) down to a new desired location. Once housed at a new location, click away from the button to de-select it.

This places the button to a new location, from where you can once again run the macro.

Copyright © 2001 Selfonline-Education. All rights reserved.