MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Restoring a Deleted Worksheet Menu Bar


Posted by Stan on October 01, 2001 5:44 AM

I used the code below to hide and then view the items in the Worksheet Menu Bar. Unfortunately the program hung and I lost everything except the File and Edit commands. Nothing was saved on sheet 5. Is there a way to restore (at least the default) settings for the menu bar without reinstalling Excel?

Cheers

Stan

*********************************
Sub HideWorkSheetMenuBar ()
Dim ctl as CommandBarControl

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

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
End Sub


Sub RestoreWorkSheetMenuBar ()
Sheet5.Activate
Range (“B1”).Select

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


Posted by Mark O'Brien on October 01, 2001 5:51 AM

Try this:

Right Click on the Toolbar and choose "Customize".
Go to the Tab called "Toolbars".
Select the toolbar called "Worksheet Menu Bar" (it should be the last one, or near the end if you've added other tb's)
Hit the Reset button.
Click "Yes".

Posted by Juan Pablo on October 01, 2001 5:52 AM

This will restore the default Menu Bar

Application.CommandBars("Worksheet Menu Bar").Reset

Juan Pablo

Posted by Mark O'Brien on October 01, 2001 6:06 AM

Also

Also, you may want to use this code as your Do...Loop when you are trying to make B1 menus visible:


Do While ActiveCell.Value <> vbNullString
Application.CommandBars("WorkSheet Menu Bar").Controls(ActiveCell.Value).Visible = True
ActiveCell.Offset(1, 0).Select
Loop

Posted by Stan on October 01, 2001 6:14 AM

That Worked Fine But ....

Juan - that worked except when I run my program I get the error message "Invalid procedure call or argument" at the following line:

Set NewBar = CommandBars.Add(Name:="MyCommandBar")

Any ideas? (so far you've been batting 100% with my questions - much appreciated).

Cheers - Stan

Posted by Juan Pablo on October 01, 2001 6:20 AM

Re: That Worked Fine But ....

I think the problem is that the Command Bar already exists (Because it worked fine for me)

Put this before this line

On Error Resume Next
CommandBars("MyCommandBar").Delete

Juan Pablo

------------------ Juan - that worked except when I run my program I get the error message "Invalid procedure call or argument" at the following line: Set NewBar = CommandBars.Add(Name:="MyCommandBar") Any ideas? (so far you've been batting 100% with my questions - much appreciated). - Stan

Posted by Stan on October 01, 2001 6:20 AM

Re: Also

Yhanks Mark, I'll try it once I get my latest problem sorted out (see my thread with Juan)

Stan Also, you may want to use this code as your Do...Loop when you are trying to make B1 menus visible:

Posted by Stan on October 01, 2001 6:25 AM

Re: That Worked Fine But ....

You were right Juan (again). Thanks

Stan I think the problem is that the Command Bar already exists (Because it worked fine for me) Put this before this line On Error Resume Next