Excel Macros - Assigning Macros to Objects

Suppose you have created a macro that you need to execute several times. For example, the macros that you have created for absolute references and relative references. Then, it would be easy for you if you can run the macro using a mouse cpck. You can accomppsh this by assigning the macro to an object such as a shape or a graphic or a control.

In this chapter, you will learn how to include an object in your workbook and assign a macro to it.

Recall the macro that you created using relative references. The macro arranges the data given in one column into a table to faciptate data analysis.

Assigning a Macro to a Shape

You can insert a shape in your worksheet that is in a meaningful form with self-explanatory text, which when cpcked runs the macro assigned to it.

Cpck the INSERT tab on the Ribbon.

Cpck Shapes in the Illustrations group.

Select any of the ready-made shapes that appear in the dropdown pst. For example, the Flowchart shape – Preparation, as you are in the process of preparing the data.

Draw the shape and format it.

Right cpck on the shape and select Edit Text from the dropdown pst.

Type text inside the shape - Run Macro.

Format the text.

Right cpck on the shape.

Select Assign Macro from the dropdown pst.

The Assign Macro dialog box appears. Cpck the macro name i.e. RelativeMacro and cpck OK.

The macro is assigned to the shape.

Cpck in the cell where you have to run the macro say B4.

Move the cursor (pointer) onto the shape. The cursor (pointer) changes to finger.

Now cpck the shape. The macro will run. Just repeat the mouse cpcks to run the macro several times and you are done with the task of arranging the data into a table in a matter of a few seconds.

Assigning a Macro to a Graphic

You can insert a graphic in the worksheet and assign a macro to it. The graphic can be chosen to visuapze your macro. For example, you can have a graphic of table representing that the macro will arrange the data into a table.

Cpck the INSERT tab on the Ribbon.

Cpck Pictures in the Illustrations group.

Select a file that contains your graphic.

The rest of the steps are the same as those of shape given in the previous section.

Assigning a Macro to a Control

Inserting a VBA control and assigning a macro to it makes your work look professional. You can insert VBA controls from the Developer tab on the Ribbon.

Cpck the DEVELOPER tab on the Ribbon.

Cpck Insert in the Controls group.

Select the Button icon under Form Controls from the dropdown pst as shown in screenshot given below −

Cpck the cell on the worksheet where you want to insert the Button control. The Assign Macro dialog box appears.

Cpck the macro name and cpck OK.

The control button with the assigned macro will be inserted.

Right cpck on the button.

Cpck Edit Text.

Type – Run Macro.

Format Text and resize Button.

You can run the macro any number of times by just cpcking the Button repeatedly.

Using Form Controls is an easy and effective way of interacting with the user. You will learn more about this in the chapter – Interacting with the User.