Creating Custom Menus the Easy

When Excel 97 was released, I was a bit frustrated with the amount of code required to create a custom menu, so I developed a technique that simplifies the process considerably. My technique uses a worksheet, shown in Figure 23-6, to store information about the new menu. A VBA procedure reads the data in the workbook and creates the menu, menu items, and submenu items. The worksheet consists of a table with five columns:

♦ Level: This is the location of the particular item relative to the hierarchy of the menu system. Valid values are 1, 2, and 3. Level 1 is for a menu; 2 is for a menu item; and 3 is for a submenu item. Normally, you'll have one level 1 item, with level 2 items below it. A level 2 item might or might not have level 3 (submenu) items.

♦ Caption: This is the text that appears in the menu, menu item, or submenu. To underline a character, place an ampersand (&) before it.

♦ Position/Macro: For level 1 items, this should be an integer that represents the position in the menu bar. For level 2 or level 3 items, this is the macro that executes when the item is selected. If a level 2 item has one or more level 3 items, the level 2 item might not have a macro associated with it.

♦ Divider: Enter True if a separator bar should be placed before the menu item or submenu item.

♦ FacelD: This optional entry is a code number that represents the built-in graphic images displayed next to an item.

Figure 23-7 shows the menu that was created from the worksheet data.

|[JTMH!W1W

aker.xls

HEE3

A B C

E

F-

1

Levef Caption

Position/Macro'' Divider1

Fncel^

-

2

1

SMyMenu

10

3

2

Wi&zards

4

3

Wizard Number &1

DummyMacro

149

5

3

Wizard Number &2

DummyMacro

36

6

3

Wizard Number &3

DummyMacro

TRUE

52

7

2

&Tools

8

3

Tools Number

DummyMacro

307

9

3

Tools Number &2

DummyMacro

352

10

2

&Printing

11

3

Printing Number &1

DummyMacro

149

12

3

Printing Number &2

DummyMacro

329

13

3

Printing Number &3

DummyMacro

TRUE

197

14

2

StCharts

15

3

Charts Number

DummyMacro

235

16

3

Charts Number ¿1

DummyMacro

136

17

2

StMinimize

DummyMacro

TRUE

345

18

2

Ma&ximize

DummyMacro

326

19

2

&Help

TRUE

20

3

StHelp Contents

DummyMacro

51

21

3

StMore Help

DummyMacro

102

22

3

SAbout

DummyMacro

TRUE

251

23

H

H <

► H[\Menu5heet A

M

•II

3

Figure 23-6: The information in this worksheet is used to create a custom menu.

Window

Mylvlenn | Help

Type a question 1

-C-li

Wizards

100

4 - ® E

a $ %

Tools

; -

Printing

F

Charts

1 1 J 1 K

m +

Minimize Maximize

Help

afee

Help Contents

•x

More Help

âl

Ahont K

Figure 23-7: This menu was created from the data stored in a worksheet.

Figure 23-7: This menu was created from the data stored in a worksheet.

This workbook is available on the companion CD-ROM.

To use this technique in your workbook or add-in, follow the steps described next.

1. Open the example workbook from the CD-ROM.

2. Copy all the code in Modulel to a module in your project.

3. Add procedures such as the following to the code module for the

ThisWorkbook object:

Private Sub Workbook_Open()

Call CreateMenu End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call DeleteMenu End Sub

4. Insert a new worksheet and name it MenuSheet. Better yet, copy the MenuSheet from the example file.

5. Customize the MenuSheet to correspond to your custom menu.

There is no error handling in the example workbook, so it's up to you to make sure that the menu is created properly.

0 0

Responses

  • CAREY CURRY
    How to create a simple menu in vba?
    2 years ago

Post a comment