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
