On Error Resume Next

For Each Menu In CommandBars(1).Controls For Each MenuItem In Menu.Controls

For Each SubMenuItem In MenuItem.Controls Cells(Row, 1) = Menu.Caption Cells(Row, 2) = MenuItem.Caption Cells(Row, 3) = SubMenuItem.Caption Row = Row + 1 Next SubMenuItem Next MenuItem Next Menu End Sub

Notice that I use On Error Resume Next to ignore the error that occurs if a menu item doesn't have any submenu items. Figure 20-3 shows part of the output from this procedure. This code is available on this book's Web site.

Figure 20-3:

List all menus, menu items, and submenu items in the Worksheet menu bar.

d-

lisl menu stuff.xls

"iOIXi

■M

ü

S

B

■r*

G iX1

1

¿Fils

■atfisw...

j

2

aFils

aSpen

_Ii

■3:

aFile

®oss

?<®

aFile

aSave

5

aFile

Savg-SAs...

6-

■aFile

.Save, as-Web PaSäe.

7

aFile

Save ¿Workspace...

8

aFile

fili'äSjSffifc.

3

aFile

Permission

m

aFile

Per&missiön

Unrestricted Access

M

aFile

Per&mission

Do Not Distribute...

12

aFile

P ermiss ton

13

aFile

Permission

14

aFile

Per'&mission

15

■aFile

Peramissioii

16

■aFile

Per&mission

17

¿File

Per&mrssion

IB:

aFile

Permission

19

aFile

Permission

m

aFile

Permission

21

aFile

Peramission

.22

aFile

Peramission

23

aFile

Peramission

S

n *

► • ti.J

1

<1_

iiji

l>!| "

0 0

Post a comment