Executing a procedure from a custom menu

As I describe in Chapter 23, Excel provides two ways for you to customize its menus: choosing the View ^ Toolbars ^ Customize command or writing VBA code. The latter method is preferable if you create applications, but you can use either technique to assign a macro to a new menu item.

Following are the steps required to display a new menu item on a menu and to assign a macro to the menu item. It assumes that the new menu item is on the Data menu, that the menu item text is Open Customer File, and that the procedure is named OpenCustomerFile.

1. Choose the View^ Toolbars ^ Customize command. Excel displays the Customize dialog box.

When the Customize dialog box is displayed, Excel is in a special "customization" mode. The menus and toolbars are not active, but they can be customized.

2. Click the Commands tab in the Customize dialog box.

3. Scroll down and click Macros in the Categories list.

4. In the Commands list, drag the first item (Custom Menu Item) to the bottom of the Data menu (after the Refresh Data menu item). The Data menu drops down when you click it.

5. Right-click the new menu item (Custom Menu Item) to display a shortcut menu.

6. Enter a new name for the menu item — &Open Customer File — in the Name text box (see Figure 9-3).

7. Click Assign Macro on the shortcut menu.

8. In the Assign Macro dialog box, select the OpenCustomerFile procedure from the list of macros.

9. Click OK to close the Assign Macro dialog box, and click Close to close the Customize dialog box.

E Microsoft Excel - corp.xls

IBB

ä] Eile

Edit View Insert Foimnt Tools

Data j Window

Help

Type a question for help * . S x 1

: j Ö y L¿ ¿a i sí a i y & i * & m, -

Eilter

-•I

; Arial

g| id g

B / U

•1

MIO

f*

y !

Form... Subtotals... Validation... Table-

1 1

A B

n

j

K

L

M

3

1

2

10

Text to Columns...

G ion p anil Outline ►

H

To add a command to a toolbar: select a category command out of this dialog box to a toolbar. Categories: Commands!

PivotTable and PivotChart Report... Im p o rt E xte ma 1 Data ► List ► XML ►

"-

Format Drawing

-

A

Close Save

1

15

Refresh Data

Custom 1

'-

q

20

Delete

Name: 1 &Open Customer File ||

0

Copy Button Image Paste Button Imajje Reset Button Image Edit Button Image... Change Button Image ►

21

24

1 Close

-

-

2b 26 27

Default Style lext Only (Always) Text Only (in Menus) Image and Text

28

q

29

31

33"

--

Begin a Group

-

_

34

Assign Hyperlink ► Assign Macro...

36

-

LV "

KSheet1/

hi i ±j r

Figure 9-3: Changing the text for a menu item.

After you follow the process mentioned above, the new menu item always appears on the menu, even when the workbook that contains the macro is not open.In other words, changes that you make via the View ^ Toolbars ^ Customize command are permanent. Selecting the new menu item opens the workbook if it's not already open.

Refer to Chapter 23 to learn how to use VBA to create menu items that are displayed only when a particular workbook is open.

Was this article helpful?

0 0

Post a comment