Buttons with VBA Macro

---

With the use of buttons, we are able to create a user-friendly interface for the use of macros.

---

Insert Button (Form Controls)

There are two types of elements that can be inserted from the Insert button in the Developer tab:

• Form Controls
• ActiveX Controls

For now we will use the Form Controls, which are simpler.

1. Go to the Developer tab
2. Click on Insert
3. Choose Button (Form Control) under Form Controls
4. Adjust the size and location of the button you want by holding down and dragging the left mouse button. Then release when you are done.
5. Select a macro and click Ok (or record a new macro)
6. Click outside the button to exit edit mode

To rename a button, right-click it and select Edit Text.

---

Insert Shapes with VBA Macro

You can also associate a shape with a macro:

1. Click the Insert tab
2. Click on Shapes
3. Select a shape (a rectangle for instance)
4. Create the shape by left-clicking and dragging the mouse
5. Name the shape (just click it and start typing)
6. Right-click the shape and select "Assign Macro..."
7. Select a macro to be assigned to the shape and click Ok (or record a new macro)

---

Using a Macro with Button (or Shape)

If you do not have a macro recorded at the time you inserted the button (or shape), you can record one and assign it to the button later.

To assign a macro to a button (or a shape) just right-click it, and select "Assign Macro...".

For learning purposes, create with the macro recorder a simple macro that changes the color of the text to green. Assign this macro to the button we previously inserted into the worksheet.

Remember to use relative references.

In this example, we started recording a macro and then we added a color to the text by clicking Home $\rightarrow$ Font $\rightarrow$ Font Color ().

Macro assigned to the Button:

Sub Macro1()
'
' Macro1 Macro
'

'
    With Selection.Font
        .Color = -12597620
        .TintAndShade = 0
    End With
End Sub

Now just select one or more cells with text, click the button and the color of the text will turn green.

---

---

Consolidating Your Learning

Suggested Exercise

---

• Previous page
• Next page

---

• report error/feedback