Hiding Excel Menus

[Marston]

I have the following code to hide Toolbars - and have been trying to
develop a parallel to hide menus. This would keep me from having to
idnetify
each one through a line like: CommandBars(1).Controls("xxxx").Visible
= False, but I can't seem to get it to work. In particular, I don't
know what the type is (I can tell its # 10 what ever that is) and I
don't seem to be able to assign the name (e.g. CBSheet.Cells(CBNum,1)
= CB.Name. See below.

Any thoughts?

Sub
Dim TB As CommandBar
Dim TBNum As Integer
Dim TBSheet As Worksheet
Set TBSheet = Sheets("TBSheet")
Application.ScreenUpdating = False
TBSheet.Cells.Clear
TBNum = 0
For Each TB in CommandBars
If TB.Type = msoBarTypeNormal Then
If TB.Visibile Then
TBNum = TBNum + 1
TB.Visible = False
TBSheet.Cells(TBNum,1) = TB.Name
End If
End If
Next TB
' Add back items desired
Application.CommandBars("Visual Basic").Visible = True
Application.ScreenUpdating = True
End Sub

 

[Bob Umlas]

commandbars("Worksheet menu bar").Enabled=False
Bob Umlas
Excel MVP