Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
Receiving run-time error only part of the time | Excel Discussion (Misc queries) | |||
run-time error '91'-Close Button error | Excel Discussion (Misc queries) | |||
Run time error 1004, General ODBC error | New Users to Excel |