Run time error
 

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

Run time error
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ub" wrote:

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

Run time error
 

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

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ub" wrote:

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