Hidden Tool Bars ---Lost!

[Nigel Smith]

I copied some code from a help website: see below to try
and auto hide a menu for a worksheet.. but this code has
hidden all the menus on any excel worksheet I open..
Please can someone advise how to get the menus back?

Many Thanks...

This code will decide if the user has closed your
Workbook or simply Activated another. This code (unless
changed) assumes you have a Custom Toolbar
called "MyToolBar" which is attached to the Workbook.
Whenever the user closes or deactivates the Workbook, all
Toolbars and Menubars will be restored as before.

To attach your a Custom Toolbar go to
View>Toolbars>Customize-Attach then Copy your Custom
Toolbar to the Workbook.


'Module level declaration
Dim IsClosed As Boolean, IsOpen As Boolean

Private Sub Workbook_Activate()
'Show the Custom toolbar
IsClosed = False
If IsOpen = False Then
Application.ScreenUpdating = False
Run "HideMenus"
Application.ScreenUpdating = True
End If
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
IsClosed = True 'Closing so set to True
If Cancel = True Then IsClosed = False 'Changed
their mind
End Sub


Private Sub Workbook_Deactivate()
Application.ScreenUpdating = False
IsOpen = False

On Error Resume Next 'In case it's already gone.
If IsClosed = True Then 'Workbook is closing.
With Application.CommandBars("MyToolBar")
.Protection = msoBarNoProtection
.Delete
End With
Run "ShowMenus"
Else 'They have only activated another Workbook
Run "ShowMenus"
End If
Application.ScreenUpdating = True
End Sub


----------------------------------------------------------
----------------------
The code below here must be placed within a Standard
Module.
It also assumes you have a hidden sheet with the CodeName
of Sheet3.
----------------------------------------------------------
----------------------

'Module level declaration
Dim Allbars As CommandBar
Dim i As Integer, BarName As String
Dim FormulaShow As Boolean

Sub HideMenus()
i = 0
Sheet3.Range("C1:C50").Clear
On Error Resume Next
For Each Allbars In Application.CommandBars
If Allbars.Visible = True Then
i = i + 1
With Sheet3
.Cells(i, 3) =
Allbars.Name
If Allbars.Name
= "Worksheet Menu Bar" Then
Allbars.Enabled =
False
Else
Allbars.Visible =
False
End If
End With
End If
Next
Application.DisplayFormulaBar = False
With Application.CommandBars("MyToolBar")
.Visible = True
.Position = msoBarTop
.Left = 0
.Protection = msoBarNoMove
End With
On Error GoTo 0
End Sub


Sub ShowMenus()
On Error Resume Next
With Sheet3
For i = 1 To WorksheetFunction.CountA
(.Columns(3))
BarName = .Cells(i, 3)
Application.CommandBars
(BarName).Enabled = True
Application.CommandBars
(BarName).Visible = True
Next i
i = 1
With Application.CommandBars("MyToolBar")
.Protection = msoBarNoProtection
.Visible = False
End With
Application.DisplayFormulaBar = True
End With
On Error GoTo 0
Application.CommandBars("Worksheet menu bar").Enabled =
True
End Sub

 

[Bob Phillips]

with application
.commandbars("Worksheet Menu Bar").enabled=true
.commandbars("Formatting").visible=true
.commandbars("Standard").visible=true
.commandbars("Cell").visible=true
.commandbars("Ply").visible=true
end with

should get you started

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)