Other Methods of Running Macros

So far, you have learned three methods of running macros. You already know how to run a macro by choosing Tools | Macro | Macros. Unfortunately, this method of running a macro is not convenient if you need to run your macro often. You also ran the macro in the VBE Code window with the keyboard shortcut F5 or by choosing Run | Run Sub/UserForm. In addition, you can run a macro from the Visual Basic Editor window by clicking a button on the Standard toolbar (Figure 1-7) or choosing Tools | Macro.

I Standard

-

B U t H ü- ¡ft ° r

[j^ll . M " ^EfW ©

Ln 184, Coll

Figure 1-7: The Visual Basic procedure can be run from the toolbar button.

Running the Macro Using a Keyboard Shortcut

A popular method to run a macro is by using an assigned keyboard shortcut. It is much faster to press Ctrl+Shift+D than it is to activate the macro from the Macro dialog box. Before you use the keyboard shortcut, you must assign it to your macro.

1. Press Alt+F8 to quickly open the Macro dialog box.

2. In the list of macros, click the name of the WhatsInACell macro, and then choose the Options button.

3. The Macro Options dialog box appears, as shown in Figure 1-8. The cursor is located in the Shortcut key text box.

Figure 1-8:

Using the Macro Options dialog box, you can assign a keyboard shortcut for running a macro.

Figure 1-8:

Using the Macro Options dialog box, you can assign a keyboard shortcut for running a macro.

4. Hold down the Shift key and press the letter I on the keyboard. Excel records the keyboard combination as Ctrl+Shift+I.

5. Click OK to close the Macro Options dialog box.

6. Click Cancel to return to the worksheet.

Tip 1-8: Avoid Shortcut Conflicts

If you assign to your macro a keyboard shortcut that conflicts with the Microsoft Excel built-in shortcut, Excel will run your macro if the workbook containing the macro code is currently open.

To run your macro using the newly assigned keyboard shortcut, make sure the Microsoft Excel window is active and press Ctrl+Shift+I.

Running the Macro from a Menu

If you'd rather work with the menus, you can add your macro as a menu option. Using the Customize dialog box, you can quickly add your own menu command to any of Excel's built-in menus.

1. In the Microsoft Excel window, right-click the empty area of the menu bar and select Customize from the shortcut menu.

2. In the Customize dialog box, click the Commands tab.

3. In the Categories list box, select Macros.

Figure 1-9:

Creating a custom menu item (Step 1)

4. Drag Custom Menu Item to the Tools menu. As the menu opens up, drop the button image to the position where you want to place it. Figure 1-10 shows the custom command placed as the last option on the Tools menu.

5. To change the name of the custom menu item, right-click the menu item and edit the text in the Name item on the shortcut menu (Figure 1-11). For this example, change the name to Contents of Ce&lls.

The ampersand (&) character is used to indicate a keyboard shortcut. Place the ampersand immediately before the letter you want to be underlined. If you named the custom option Contents of Ce&lls, the custom menu option will be displayed as Contents of Cells. Notice that menu options can contain spaces between the words.

6. To assign a macro to the Contents of Cells menu option, select the last option—Assign Macro—on the shortcut menu (Figure 1-11). In the Macro dialog box, select the WhatsInACell macro and click OK. Click Close to close the Customize dialog box.

Figure 1-9:

Creating a custom menu item (Step 1)

AB

6

A

B C

1

2

Category

Jan Feb

3

Pencils

n jj

<!

Pene

I0 35

5

Total

22 50

S

7

B

y

10

il

12

13

"14

15

16

17

18

19

20

¿I

22

Qoal Seek... Scsnaiios... Foimula Auditing

(Jacio

Ai*oCoirsct Opliors...

Customize..

Options.,.

Custom Menu Item

Joo's ! Dala Window

Spdlng Eirof Checking Speech

ShaeWoikbock, , Iiack Changes

Ccrnpore end Meroe ^¿oikbcoks...

Eioteolion

Orine Collaboration

Qoal Seek... Scsnaiios... Foimula Auditing

(Jacio

Ai*oCoirsct Opliors...

Customize..

Options.,.

Custom Menu Item aq-ieslia-!tohefc -»Sx

"Lir^

bars £cmmancfc ] Options |

dd a command to a toobar : select a category and drag île mond out of this dialog box to a todbor, Commands;

"3 Custom Menu Item

(S) Custom Button bars £cmmancfc ] Options |

dd a command to a toobar : select a category and drag île mond out of this dialog box to a todbor, Commands;

"3 Custom Menu Item

(S) Custom Button

Figure 1-10: Creating a custom menu item (Step 2). You can place the custom option in any Excel menu or submenu.

Figure 1-11: Creating a custom menu item (Step 3). Using the shortcut menu, you can rename the menu option and assign your own macro to it. To use this shortcut menu, you must first open the Customize dialog box.

Your macro can now run from your custom menu option. If you close the shortcut menu before assigning a macro to the menu option, Excel will prompt you for the macro name when you attempt to use your menu option for the first time.

7. Choose Tools | Contents of Cells to run your macro, or press Alt+T and the letter l.

If you removed a built-in menu or menu option while performing the above steps, open the Customize dialog box, click the Toolbars tab, and choose the Reset button. While this will bring back Microsoft Excel default options, your custom menu option will be removed.

Running the Macro from a Toolbar Button

If you like to use toolbar buttons, you can easily add a custom button to any toolbar and assign it to your own macro. Let's add the WhatslnACell macro to a toolbar.

1. Choose Tools | Customize.

2. In the Customize dialog box, click the Commands tab.

3. In the Categories list box, select Macros.

4. Drag the Custom Button image to the position on the toolbar where you want to place the button. In this example, the button is added to the Standard toolbar to the right of the Format Painter button.

5. To change the tooltip of the button, right-click the button and edit the text in the Name item on the shortcut menu. For this example, change the name of the button's tooltip to Contents of Ce&lls.

6. To change the image on the button, right-click the button and select the Change Button Image command from the shortcut menu. Forty-two predesigned images included with Excel will appear. Select the image you want. For this example, the default custom image was replaced with the pencil image.

7. To assign the macro to the button, right-click the button to open the shortcut menu and choose the Assign Macro command.

8. Select the WhatsInACell macro and choose OK.

9. Click the Close button to close the Customize dialog box.

10. Point the mouse to the custom button you just created. The Contents of Cells tooltip appears next to the button (Figure 1-12). Make sure the active sheet contains a spreadsheet with text, numbers, and formulas. Then click the custom button to run the macro.

Microsoft Excel - Chap01.xls

QhGO

Arial

File Edit View Insert Format Iools Data Window Help lype a question tor help - _ i1

AB _f*_ HContents-pi Cellsl U

Arial

File Edit View Insert Format Iools Data Window Help lype a question tor help - _ i1

AB _f*_ HContents-pi Cellsl U

A

E

B

D

E

F

m

H

I

-

1

2

Category

Jan

Feb

Mar

1 st Qtr.

3

Pencils

12

22

I5S

Figure 1-12: You can add a custom button to any toolbar to run your macro.

Figure 1-12: You can add a custom button to any toolbar to run your macro.

Running the Macro from a Worksheet Button

Later in this book, you will learn how buttons placed in a worksheet can help beginning Excel users with data entry. For now, let's go over the steps that will attach the WhatsInACell macro to a worksheet button.

1. Activate the example worksheet containing the data.

2. Choose View | Toolbars and select Forms. The Forms toolbar appears, as shown in Figure 1-13.

3. On the Forms toolbar, click Button.

4. Click anywhere in the worksheet.

5. When the Assign Macro dialog box appears, choose the name of the macro (WhatsInACell) and click OK.

6. To change the name of Button 1, make sure the button is selected, and enter the name Contents of Cells. When the button is selected, it looks like the one shown in Figure 1-13. If the selection handles are not displayed, right-click Button 1 on the worksheet and choose Edit Text on the shortcut menu. Select the default text and enter the new name.

7. When you're done renaming the button, click anywhere in the worksheet and outside the button to exit the Edit mode.

8. To run your macro, click the button you just created.

Figure 1-13: You can attach your macro to a button placed in a worksheet.

Liit Bok Toggle Finition — Scroll B-ïr — Imsgs —

Moue Controls

Liit Bok Toggle Finition — Scroll B-ïr — Imsgs —

Moue Controls

Ü

17

[ibi

m

IS

>4

H

1

A

-Te^t Boit

" Spul Button

- Label

-Te^t Boit

" Spul Button

- Label

Figure 1-14:

Default tools in the Control Toolbox

Tip 1-9: Adding Controls to a Worksheet

You can add controls to a worksheet using the Forms toolbar (see Figure 1-13) or the Control Toolbox (see Figure 1-14). Both toolbars can be accessed from the View menu by selecting the Toolbars option. Controls accessed via the Forms toolbar are compatible with earlier versions of Excel (5.0, 7, and 97) and can be used on chart sheets, old XLM macro sheets, and worksheets when all you want to do is run a macro by clicking a control. The controls in the Control Toolbox are known as ActiveX controls. You can place the ActiveX controls on worksheets or your custom forms that you create by using the Visual Basic Editor. While the controls located on the Forms toolbar can only respond to the Click event, the ActiveX controls have many different actions, or events, that can occur when you use the control. When you use a control from the Forms toolbar, you assign a macro to it that is stored in a module of This Workbook, New Workbook, or Personal Macro Workbook. When you use an ActiveX control, write macro code that is stored with the control itself.

0 0

Post a comment