Hide all Command Bars on open

Hi Dominic

Here is some code I use to hide all standard Excel menu bars on activation and show only a custom menu bar and then restore as were on deactivation. Place the below the Workbook module:

The custom menu bar should be attached to the workbook.

Option Explicit

Dim IsClosed As Boolean

Private Sub Workbook_Activate()
'Show the Custom toolbar
Application.ScreenUpdating = False
Run "HideMenus"
Application.ScreenUpdating = True
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
On Error Resume Next 'In Case it's already gone.
If IsClosed = True Then 'Workbook is closing
With Application.CommandBars("MyCustomBar")
.Protection = msoBarNoProtection
.Delete
End With
Run "ShowMenus"
Else 'They have activated another Workbook
Run "ShowMenus"
End If
Application.ScreenUpdating = True
End Sub

Then this is placed in a normal module:

Option Explicit
Dim Allbars As CommandBar
Dim i As Integer, BarName As String
Dim FormulaShow As Boolean
Sub HideMenus()
i = 0
Sheet3.Columns(3).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("MyCustomBar")
.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("MyCustomBar")
.Protection = msoBarNoProtection
.Visible = False
End With
Application.DisplayFormulaBar = True
End With
On Error GoTo 0
End Sub

As you will see I use a hidden sheet (Sheet3) to keep track of all menubars that were previously showing and then restore them.

This should give you the general idea. You could of course use Full Screen mode, but it has it's drawbacks.

Dave

OzGrid Business Applications