Hi skadan,
You could try a simple "For Each ..." statement to loop through all the commandbars.
You may want to look at the following code from Dave Hawley's site:
http://www.ozgrid.com/
Hide all of Excels standard Menus and Toolbars and show only your Custom Toolbar.
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
HTH
