MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hiding Specific Items in a Menu Bar


Posted by Stan on September 28, 2001 9:45 AM

In my previous posting on this subject (see below) I should have also asked how to restore the Menu Bar controls. This is the way I think it should be done but before trying it (and obliterating my controls) does anyone see any problem with the code?

Sub HideMainMenu ()
Dim ctl as CommandBarControl

‘Write name of current menu bar controls to sheet 5
Sheet5.Activate
Range (“A2”).Select

For Each ctl in Application.CommandBars(“Worksheet Menu Bar”).Controls
ActiveCell.Value = ctlName
If ctl.Index >=3 then
ctl.Visible = False
end if
Next ctl


Sub RestoreMainMenu ()
Sheet5.Activate
Range (“A2”)

Do While ActiveCell.Value <> vbNullString
Application.CommandBars(“WorkSheet Menu Bar”, ActiveCell.Value).Visible
ActiveCell.Offset(1,0)>select
Loop
End Sub

Cheers

Stan
*************************************************

Posted by Juan Pablo on September 28, 2001 at 07:11:08:

In Reply to: Hiding Specific Items in a Menu Bar posted by Stan on September 28, 2001 at 07:05:40:

Use this:

Sub hide()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars("Worksheet Menu Bar").Controls
If ctl.Index >= 3 Then ctl.Visible = False
Next ctl
End Sub

Juan Pablo

---------------------


Posted by Barrie Davidson on September 28, 2001 10:08 AM

Stan, after a quick scan, I would make one change:

For Each ctl in Application.CommandBars(“Worksheet Menu Bar”).Controls
ActiveCell.Value = ctlName
If ctl.Index >=3 then
ctl.Visible = False
end if
Activecell.Offset(1,0).Activate
Next ctl

Added the second last line (so that you don't overwrite the control name each time in your For loop).

Barrie
Barrie Davidson