What must you add to the Excel ribbon before you can insert a form control or create a macro

Categories: Macros

If you’ve ever repeated the same actions multiple times in a spreadsheet, you’ll benefit from recording a macro to automate those tasks. But, once you have a macro, how do you access it?

The easiest way is to add a macro button to your spreadsheet, for which Excel offers three straightforward methods:

• Add a button to the Quick Access Toolbar;
• Add a form control button; or
• Add an ActiveX control button.

In this example, we’ll add a button for the macro titled “Say_Hello.” Before you can use any of these methods, however, you must have the Developer tab available on the ribbon.

Show the Developer Tab

Look at the far right of your Excel ribbon. If there is no “Developer” tab, then add it:

1. From the “File” menu, choose “Options.”
2. Choose “Customize the Ribbon.”
3. Click the checkbox for “Developer.”

1. Add a Button to the Quick Access Toolbar

The simplest way to add a button for “Say_Hello” is to add it to the Quick Access toolbar, which is displayed in the Excel window title bar.

1. Right-click a blank area of the ribbon.
2. Choose “Customize Quick Access Toolbar.”
3. From the dialog box that appears, choose “Macros” for “Choose commands from:” and select your macro from the list.
   
4. Click “Add >>” and “Save.”

Your “Say_Hello” button now appears on the Quick Access toolbar.

Although this is the easiest method, it is also the least flexible. Your “Say_Hello” button can’t appear anywhere except in the Quick Access Toolbar, and it can do nothing but run a macro.

2. Add a Form Control Button

More flexible than the Quick Access toolbar, a form control button can go anywhere in your workbook, and you control the position, size, text, and style.

1. Choose the “Developer” ribbon.
2. Choose “Insert | Button (Form Control).”
   
3. Click and drag a rectangle for the “Say_Hello” button.
4. When you release your mouse button, a macro dialog box will appear.
   
5. Click the name of the macro that you wish to run when the user presses the “Say_Hello” button, then choose “OK.” If you haven’t yet created the macro, you can create it now with the “New” button or you can record one with the “Record…” button.

With your new “Say_Hello” button is in place, feel free right-click it, choose “Properties,” and modify the font, size, position, and other attributes.

3. Add an ActiveX Control Button

The third option, an ActiveX control button, is more powerful than a form control button because it harnesses the full range of the Visual Basic language. To create an ActiveX control button:

1. Choose the “Developer” ribbon.
2. Choose “Insert | Button (Form Control).”
   
3. Click and drag a rectangle for the “Say_Hello” button.
4. Right-click the “Say_Hello” button and choose “View Code.”
   
5. This will bring up the Visual Basic editor.
   
6. Be sure that the listbox in the upper right-hand corner shows “Click.”
7. Edit your code.
8. Save and exit the Visual Basic editor.

The ActiveX control takes a little longer than the form control to set up; however, once finished, any instructions in the “Click” subroutine will run when the user clicks the button.

Additional Options

For a quick and easy button, use Quick Access toolbar buttons or form control buttons. For the most flexibility, use ActiveX controls, which allow you to employ the full range of the Visual Basic programming language. Experiment with the language, and you’ll learn how to build a full-featured application, all from within your spreadsheet.