List Visible Controls in CommandBars("Worksheet Menu Bar")

[RyanH]

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?

 

[Jon Peltier]

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

 

[RyanH]

Yep that was it! Do you have any idea on Question #2?

This isn't making sense to me:

In the immediate window:
?Application.CommandBars("Worksheet Menu Bar").Controls.Count = 12

But when I use this code below it only lists 11 controls, why?

Sub ListUserControls()

Dim ctrl As CommandBarControl
Dim rw As Long

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

End Sub

Here is the list below:
&File
&Edit
&View
&Insert
F&ormat
&Tools
&Data
A&ction <<==What is this?
&Window
Macola Enterprise Suite
&Help
Ado&be PDF

I tried this line below in the immediate window and the A&ction will not
show, do you know what it is?
Application.CommandBars("Worksheet Menu Bar").Controls(8).Visible = True
--
Cheers,
Ryan

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
_______

 

[Jon Peltier]

I have heard of this menu, and I used to know something about it. But it's
rather obscure, and it's been a long while. If it helps, this menu has no
menu items.

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

Yep that was it! Do you have any idea on Question #2?

This isn't making sense to me:

In the immediate window:
?Application.CommandBars("Worksheet Menu Bar").Controls.Count = 12

But when I use this code below it only lists 11 controls, why?

Sub ListUserControls()

Dim ctrl As CommandBarControl
Dim rw As Long

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

End Sub

Here is the list below:
&File
&Edit
&View
&Insert
F&ormat
&Tools
&Data
A&ction <<==What is this?
&Window
Macola Enterprise Suite
&Help
Ado&be PDF

I tried this line below in the immediate window and the A&ction will not
show, do you know what it is?
Application.CommandBars("Worksheet Menu Bar").Controls(8).Visible = True