Disable all toolbars apart from custom toobar

[Lisa]

Hi
I am using the following to disable all command bars when opening up my
worksheet within the Private Sub Workbook_Open()

Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB
End Sub

And setting it to True when I close the workbook within Private Sub
Workbook_BeforeClose(Cancel As Boolean).

I then wanted to enable my custom toolbar when all the others are disabled,
but I get an error. So to get around this, I used

Application.CommandBars(" comand bar name ").Enabled = True/False to switch
off and switch back on what I wanted

The problem is, I also use SaveCopyAs to give the worksheets a unique name
when they are saved. When they close, the beforeclose event kicks in and my
original workbook remains there with all toolbars visible.

Can anyone help?

 

[Ron de Bruin]

Hi Lisa

From
http://www.rondebruin.nl/menuid.htm

You can do

Sub Disable_Command_Bars_2()
'This will disable all BuiltIn Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.BuiltIn = True Then
Cbar.Enabled = False
End If
Next
End Sub

Sub Enable_Command_Bars_2()
'This will Enable all BuiltIn Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.BuiltIn = True Then
Cbar.Enabled = True
End If
Next
End Sub
You can set
Application.EnableEvents = False to disable the events
Set it to true when the code is ready

 

[Lisa]

Thanks very much, will check it out

Hi Lisa

From
http://www.rondebruin.nl/menuid.htm

You can do

Sub Disable_Command_Bars_2()
'This will disable all BuiltIn Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.BuiltIn = True Then
Cbar.Enabled = False
End If
Next
End Sub

Sub Enable_Command_Bars_2()
'This will Enable all BuiltIn Command bars
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
If Cbar.BuiltIn = True Then
Cbar.Enabled = True
End If
Next
End Sub

You can set
Application.EnableEvents = False to disable the events
Set it to true when the code is ready