Assigning a Macro to a Shape

A shape is a graphical object that can be inserted into an Excel worksheet.

Examples include:

• Rectangle
• Circle
• Arrow
• Rounded Rectangle
• Callout
• Flowchart shapes

Shapes can be used for designing worksheets and creating user-friendly controls.

A shape can work like a clickable button. When a macro is assigned to it, clicking the shape runs that macro.

Click Shape
     ↓
Run Macro
     ↓
Perform Excel Task

This can make an Excel workbook easier to use.

Excel provides Form Control buttons for running macros, but shapes can also be used as clickable macro controls.

Shapes provide more design options because you can change their size, fill, outline, text, and other appearance properties.

Suppose you have a macro named:

GenerateReport

You can insert a rectangle shape and write:

Generate Report

After assigning the macro, clicking the shape will run the GenerateReport macro.

Before assigning a macro to a shape, create a macro that performs a task.

For example:

Sub WelcomeMessage()

    MsgBox "Welcome to Excel Automation!"

End Sub

This macro displays a message when it runs.

To insert a shape, go to the Insert tab on the Excel Ribbon.

Look for the Shapes option in the Illustrations group.

Insert → Shapes

Click Insert → Shapes and choose the shape you want to use.

For a simple macro control, a Rectangle or Rounded Rectangle is commonly suitable.

After selecting a shape, click and drag on the worksheet to draw it.

You can control the size of the shape by dragging its edges or corners.

Insert Shape
     ↓
Click and Drag
     ↓
Shape Appears on Worksheet

You can add text inside a shape to explain what it does.

For example:

Generate Report

The text makes the purpose of the shape clear to the user.

Right-click the shape and choose the option to edit its text.

Enter a meaningful name based on the task that the macro performs.

Examples:

• Calculate Result
• Generate Report
• Clear Data
• Search Student

After creating the shape, right-click it.

Choose Assign Macro from the context menu.

Right-click Shape
       ↓
Assign Macro

The Assign Macro dialog box displays available macros.

Select the macro that you want the shape to run.

For example:

GenerateReport

Then click OK.

After assigning the macro, click the shape.

Excel should run the assigned macro.

If the macro displays a message, the message box should appear when the shape is clicked.

You can change the fill color of the shape to make it visually attractive.

Select the shape and use the Shape Format options to change its appearance.

Choose a color that makes the shape easy to identify.

You can also change the outline of a shape.

The outline can be modified using the shape formatting options.

A suitable outline can help separate the macro control from other worksheet content.

The size of the shape can be adjusted according to the worksheet layout.

Make the shape large enough so users can easily click it.

Avoid making the shape unnecessarily large.

You can move the shape to any suitable location on the worksheet.

For example, macro controls can be placed near the top of a report or beside a data-entry area.

You can create multiple shapes and assign different macros to each shape.

[ Add Student ]

[ Calculate Result ]

[ Generate Report ]

[ Clear Form ]

Each shape can perform a different task.

A shape can be assigned to a macro that performs a data-entry operation.

For example, a shape labeled Add Student can run a macro that stores student information in a worksheet.

A shape can also run a calculation macro.

For example:

Calculate Result
       ↓
Run Macro
       ↓
Calculate Total
       ↓
Calculate Percentage

A report-generation macro can be assigned to a shape.

For example, a shape labeled Generate Report can run a macro that prepares a formatted report.

This provides an easy way for users to start the report process.

Shapes provide several useful features:

• Easy to click
• Easy to format
• Can contain descriptive text
• Can be resized
• Can be moved around the worksheet
• Can be connected to macros

The shape acts as a visual control, while the assigned macro performs the actual task.

Shape
  ↓
Assigned Macro
  ↓
VBA Instructions
  ↓
Excel Task

The shape provides a simple interface for the user.

You can change which macro a shape runs.

Right-click the shape and select Assign Macro.

Choose another macro from the list and click OK.

The shape will then run the newly assigned macro.

Always test a shape after assigning a macro.

1. Click the shape.
2. Check whether the correct macro runs.
3. Check the result.
4. Test the macro again if required.
5. Confirm that the shape performs the intended task.

Follow these practices when using macro shapes:

• Use meaningful text inside the shape.
• Assign the correct macro.
• Keep related controls together.
• Make shapes easy to click.
• Test every macro shape.
• Save the workbook as an .xlsm file.

The complete process is:

Create Macro
     ↓
Insert Shape
     ↓
Add Shape Text
     ↓
Right-click Shape
     ↓
Assign Macro
     ↓
Select Macro
     ↓
Click OK
     ↓
Test Shape

Shapes can be useful when creating practical Excel applications such as a student management workbook.

For example:

┌─────────────────────┐
│    Add Student      │
└─────────────────────┘

┌─────────────────────┐
│  Calculate Result   │
└─────────────────────┘

┌─────────────────────┐
│   Generate Report   │
└─────────────────────┘

Each shape can be connected to a different macro.

Try the following exercise:

1. Create a simple macro that displays a message.
2. Go to the Insert tab.
3. Click Shapes.
4. Select a Rectangle shape.
5. Draw the shape on the worksheet.
6. Add the text Click Me.
7. Right-click the shape.
8. Select Assign Macro.
9. Select your macro.
10. Click OK.
11. Click the shape.
12. Check whether the macro runs.

An Excel shape can be used as a visual control for running a macro. You can insert a shape, add descriptive text, format its appearance, and assign a macro to it.

When the user clicks the shape, Excel runs the assigned macro. This makes shapes useful for creating simple and user-friendly Excel applications.

Insert Shape
      ↓
Add Text
      ↓
Assign Macro
      ↓
Click Shape
      ↓
Run Macro