Run time error

[ub]

Hi
I have an Excell sheet that I send to diffrent users to complete a task. I
have a vba code on workbook open event to hide toolbars and menubar.
I code is :
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.OnKey "%-"
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("PROMT SmarTool").Visible = False
Application.CommandBars("Control Toolbox").Visible = False

But I am getting an error , because I don't know, what type of commandbars
are open at the user level. If I take this code out from the workbook open
event, my sheet runs perfect.
Can some advise, a better way to write vba code to make all commandbars and
menubar items invisible on workbook open and make them visible on workbook
close

Thanks in advance

 

[Luke M]

A lengthy way would be to setup variable noting the condition of each.
At beginning, setup some variables like
Bar1 = Application.CommandBars(1).Enabled
Bar2 = Application.CommandBars(2).Enabled
.....etc.

This will store all the True/False conditions.
Note the use of number rather than names, as this will help you with custom
named toolbars.

Then you can do all your:
Application.CommandBars(1).Enabled = False
....etc

Then at end, run through them all again
Application.CommandBars(1).Enabled = Bar1
Application.CommandBars(2).Enabled = Bar2
....etc

Unfortunately, a starting workbook can have 127 command bars available. But,
from the VBA help file "There is no programmatic way to return the set of
command bars attached to a workbook."

So, if you want to guarantee all command bars are hidden, it will be
tedious. If you only care about a certain few, you can just limit this code
to the bars you're concerned with.

 

[ub]

Hi Luke

Thanks for the reply
The problem is that, if I write Application.commandbars(1). enable false and
if the use already has disabled this commandbar, I get an error.
Is there a way to write a code to check, if commandbar(1) was enabled true,
than make enabled false and same way for aother few comanbars that I want
enabled false