Excel choked on the declaration of ctrl "As Control". I changed that to "As
CommandBarControl", and it worked.

Sub ListUserControls()

Dim ctrl As CommandBarControl
Dim rw As Long

rw = 2
For Each ctrl In Application.CommandBars("Worksheet Menu Bar").Controls
If ctrl.Visible = True Then
Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption
rw = rw + 1
End If
Next ctrl

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"RyanH" wrote in message
...
I would like to hide all controls in the Worksheet Menu Bar except the File
Menu. This is the code I plan to use but it doesn't work. I am getting
an
error: Type Mismatch. Can someone make sense of this?

Sub ListUserControls()

Dim ctrl As Control
Dim rw As Long

rw = 2
Error For Each ctrl In Application.CommandBars("Worksheet Menu
Bar").Controls
If ctrl.Visible = True Then
Sheets("Command Bars").Cells(rw, "B") = ctrl.Caption
rw = rw + 1
End If
Next ctrl

End Sub.

Question #2: In the immediate window I returned a Control.Caption =
A&ction.
I do not see it in the Worksheet Menu Bar, so I set its Visible property
to
True and I still couldn't see it. Anyone have any ideas of what this is
and
what it is used for?

--
Cheers,
Ryan