Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn Off Macros With Workbook Open
I have a workbook with complementary Auto_Open and Workbook_BeforeClose code.
Part of Auto_Open ensures macros are enabled. With Workbook_BeforeClose resetting the workbook for when it is next opened. Question having opened a workbook with macros enabled can a user then turn them off sometime during the use of the book, thereby disabling my Workbook_BeforeClose code. (Access to Macro code is password protected) Could not find correct placement/syntax for Application.EnableCancelKey - xlDisabled. but having tried Ctrl+Break, it has no effect as these macros are not actually running while the book is in use. Many Thanks Paul Moles |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn Off Macros With Workbook Open
Users can stop events from running by just a single line in the VBE's immediate
window: application.enableevents = false If they can turn this off, I don't think you'll find an automatic way of turning it back on. If you have macros that _must_ be run, you could toggle it back on there. But if they don't run it, you're out of luck. The good news is that most users won't know how to do that. The bad news it only takes one to share the info. Paul Moles wrote: I have a workbook with complementary Auto_Open and Workbook_BeforeClose code. Part of Auto_Open ensures macros are enabled. With Workbook_BeforeClose resetting the workbook for when it is next opened. Question having opened a workbook with macros enabled can a user then turn them off sometime during the use of the book, thereby disabling my Workbook_BeforeClose code. (Access to Macro code is password protected) Could not find correct placement/syntax for Application.EnableCancelKey - xlDisabled. but having tried Ctrl+Break, it has no effect as these macros are not actually running while the book is in use. Many Thanks Paul Moles -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn Off Macros With Workbook Open
Add a Command button in the sheet, with title 'Disable Macro".
When it is clicked, in the code for command button click, change value of a public variable, (say set it to 100) (public variable should be defined at module level.). In Before_Close code, at the begining check the value of this public variable, if it is 100 then exit sub. Sharad "Paul Moles" wrote in message ... I have a workbook with complementary Auto_Open and Workbook_BeforeClose code. Part of Auto_Open ensures macros are enabled. With Workbook_BeforeClose resetting the workbook for when it is next opened. Question having opened a workbook with macros enabled can a user then turn them off sometime during the use of the book, thereby disabling my Workbook_BeforeClose code. (Access to Macro code is password protected) Could not find correct placement/syntax for Application.EnableCancelKey - xlDisabled. but having tried Ctrl+Break, it has no effect as these macros are not actually running while the book is in use. Many Thanks Paul Moles |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn Off Macros With Workbook Open
Well, If your question is, they are doing it (disabling macros) and you want
to know how they can do it then:- One has to simply make VB toolbar visible, and enter in to 'design' mode, and close the workbook. Sharad "Paul Moles" wrote in message ... I have a workbook with complementary Auto_Open and Workbook_BeforeClose code. Part of Auto_Open ensures macros are enabled. With Workbook_BeforeClose resetting the workbook for when it is next opened. Question having opened a workbook with macros enabled can a user then turn them off sometime during the use of the book, thereby disabling my Workbook_BeforeClose code. (Access to Macro code is password protected) Could not find correct placement/syntax for Application.EnableCancelKey - xlDisabled. but having tried Ctrl+Break, it has no effect as these macros are not actually running while the book is in use. Many Thanks Paul Moles |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn Off Macros With Workbook Open
Thanks for this, is there code to turn off the VB toolbar?
Thanks Paul Moles "Sharad Naik" wrote: Well, If your question is, they are doing it (disabling macros) and you want to know how they can do it then:- One has to simply make VB toolbar visible, and enter in to 'design' mode, and close the workbook. Sharad "Paul Moles" wrote in message ... I have a workbook with complementary Auto_Open and Workbook_BeforeClose code. Part of Auto_Open ensures macros are enabled. With Workbook_BeforeClose resetting the workbook for when it is next opened. Question having opened a workbook with macros enabled can a user then turn them off sometime during the use of the book, thereby disabling my Workbook_BeforeClose code. (Access to Macro code is password protected) Could not find correct placement/syntax for Application.EnableCancelKey - xlDisabled. but having tried Ctrl+Break, it has no effect as these macros are not actually running while the book is in use. Many Thanks Paul Moles |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn Off Macros With Workbook Open
In Auto_Open Macro add following code at the top
On Error Resume Next 10 y = Application.CommandBars("Visual Basic").Controls("Design Mode").ID If Err = 0 Then Application.CommandBars("Visual Basic").Controls("Design Mode").Delete GoTo 10 End If Application.CommandBars.DisableCustomize = True On Error GoTo 0 This will delete, the Design Mode button on Visual Basic tool bar. Since someone can add many Design toolbars, the code ensures to delete all of them. The last but one line of the code then disables Customize button on the Tools Menu bar, so that the user can not add the button back. Please note that the users will not be able to customize tool bar at all, even after they close the workbook with above code or even after restarting excel. In the workbook before close event you may like to add code to allow the customization of the tool bar, which will be as under. Application.CommandBars.DisableCustomize = True Sharad "Paul Moles" wrote in message ... Thanks for this, is there code to turn off the VB toolbar? Thanks Paul Moles "Sharad Naik" wrote: Well, If your question is, they are doing it (disabling macros) and you want to know how they can do it then:- One has to simply make VB toolbar visible, and enter in to 'design' mode, and close the workbook. Sharad "Paul Moles" wrote in message ... I have a workbook with complementary Auto_Open and Workbook_BeforeClose code. Part of Auto_Open ensures macros are enabled. With Workbook_BeforeClose resetting the workbook for when it is next opened. Question having opened a workbook with macros enabled can a user then turn them off sometime during the use of the book, thereby disabling my Workbook_BeforeClose code. (Access to Macro code is password protected) Could not find correct placement/syntax for Application.EnableCancelKey - xlDisabled. but having tried Ctrl+Break, it has no effect as these macros are not actually running while the book is in use. Many Thanks Paul Moles |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn Off Macros With Workbook Open
Sorry it should be = False in the last code line in my below post.
Sharad "Sharad Naik" wrote in message ... In Auto_Open Macro add following code at the top On Error Resume Next 10 y = Application.CommandBars("Visual Basic").Controls("Design Mode").ID If Err = 0 Then Application.CommandBars("Visual Basic").Controls("Design Mode").Delete GoTo 10 End If Application.CommandBars.DisableCustomize = True On Error GoTo 0 This will delete, the Design Mode button on Visual Basic tool bar. Since someone can add many Design toolbars, the code ensures to delete all of them. The last but one line of the code then disables Customize button on the Tools Menu bar, so that the user can not add the button back. Please note that the users will not be able to customize tool bar at all, even after they close the workbook with above code or even after restarting excel. In the workbook before close event you may like to add code to allow the customization of the tool bar, which will be as under. Application.CommandBars.DisableCustomize = True Sharad "Paul Moles" wrote in message ... Thanks for this, is there code to turn off the VB toolbar? Thanks Paul Moles "Sharad Naik" wrote: Well, If your question is, they are doing it (disabling macros) and you want to know how they can do it then:- One has to simply make VB toolbar visible, and enter in to 'design' mode, and close the workbook. Sharad "Paul Moles" wrote in message ... I have a workbook with complementary Auto_Open and Workbook_BeforeClose code. Part of Auto_Open ensures macros are enabled. With Workbook_BeforeClose resetting the workbook for when it is next opened. Question having opened a workbook with macros enabled can a user then turn them off sometime during the use of the book, thereby disabling my Workbook_BeforeClose code. (Access to Macro code is password protected) Could not find correct placement/syntax for Application.EnableCancelKey - xlDisabled. but having tried Ctrl+Break, it has no effect as these macros are not actually running while the book is in use. Many Thanks Paul Moles |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn Off Macros With Workbook Open
Thanks for this.
Where / how do I use Application.EnableCancelKey - xlDisabled Many Thanks "Sharad Naik" wrote: Sorry it should be = False in the last code line in my below post. Sharad "Sharad Naik" wrote in message ... In Auto_Open Macro add following code at the top On Error Resume Next 10 y = Application.CommandBars("Visual Basic").Controls("Design Mode").ID If Err = 0 Then Application.CommandBars("Visual Basic").Controls("Design Mode").Delete GoTo 10 End If Application.CommandBars.DisableCustomize = True On Error GoTo 0 This will delete, the Design Mode button on Visual Basic tool bar. Since someone can add many Design toolbars, the code ensures to delete all of them. The last but one line of the code then disables Customize button on the Tools Menu bar, so that the user can not add the button back. Please note that the users will not be able to customize tool bar at all, even after they close the workbook with above code or even after restarting excel. In the workbook before close event you may like to add code to allow the customization of the tool bar, which will be as under. Application.CommandBars.DisableCustomize = True Sharad "Paul Moles" wrote in message ... Thanks for this, is there code to turn off the VB toolbar? Thanks Paul Moles "Sharad Naik" wrote: Well, If your question is, they are doing it (disabling macros) and you want to know how they can do it then:- One has to simply make VB toolbar visible, and enter in to 'design' mode, and close the workbook. Sharad "Paul Moles" wrote in message ... I have a workbook with complementary Auto_Open and Workbook_BeforeClose code. Part of Auto_Open ensures macros are enabled. With Workbook_BeforeClose resetting the workbook for when it is next opened. Question having opened a workbook with macros enabled can a user then turn them off sometime during the use of the book, thereby disabling my Workbook_BeforeClose code. (Access to Macro code is password protected) Could not find correct placement/syntax for Application.EnableCancelKey - xlDisabled. but having tried Ctrl+Break, it has no effect as these macros are not actually running while the book is in use. Many Thanks Paul Moles |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn Off Macros With Workbook Open
If you want users not to inturrupt the macros, then
use it in each and every procedure (i.e. every 'Sub'), and it must be the first line in each and every procedure. (Be carefull before puting this in to a procedure. Ensure that your procedure is all correct and will not go in to a infinte loop. Then only put this code in the procedure. Because once this is put, there is no way to interrupt the macro., you will have to forcefully switch off the PC.) Sharad "Paul Moles" wrote in message ... Thanks for this. Where / how do I use Application.EnableCancelKey - xlDisabled Many Thanks "Sharad Naik" wrote: Sorry it should be = False in the last code line in my below post. Sharad "Sharad Naik" wrote in message ... In Auto_Open Macro add following code at the top On Error Resume Next 10 y = Application.CommandBars("Visual Basic").Controls("Design Mode").ID If Err = 0 Then Application.CommandBars("Visual Basic").Controls("Design Mode").Delete GoTo 10 End If Application.CommandBars.DisableCustomize = True On Error GoTo 0 This will delete, the Design Mode button on Visual Basic tool bar. Since someone can add many Design toolbars, the code ensures to delete all of them. The last but one line of the code then disables Customize button on the Tools Menu bar, so that the user can not add the button back. Please note that the users will not be able to customize tool bar at all, even after they close the workbook with above code or even after restarting excel. In the workbook before close event you may like to add code to allow the customization of the tool bar, which will be as under. Application.CommandBars.DisableCustomize = True Sharad "Paul Moles" wrote in message ... Thanks for this, is there code to turn off the VB toolbar? Thanks Paul Moles "Sharad Naik" wrote: Well, If your question is, they are doing it (disabling macros) and you want to know how they can do it then:- One has to simply make VB toolbar visible, and enter in to 'design' mode, and close the workbook. Sharad "Paul Moles" wrote in message ... I have a workbook with complementary Auto_Open and Workbook_BeforeClose code. Part of Auto_Open ensures macros are enabled. With Workbook_BeforeClose resetting the workbook for when it is next opened. Question having opened a workbook with macros enabled can a user then turn them off sometime during the use of the book, thereby disabling my Workbook_BeforeClose code. (Access to Macro code is password protected) Could not find correct placement/syntax for Application.EnableCancelKey - xlDisabled. but having tried Ctrl+Break, it has no effect as these macros are not actually running while the book is in use. Many Thanks Paul Moles |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can force enable macros to be able to open my workbook? | Excel Discussion (Misc queries) | |||
Open workbook-macros enabled, opening another with macros | Excel Programming | |||
How to stop having to re-assign macros every time you open a workbook??? | Excel Programming | |||
Open workbook - prevent macros | Excel Programming | |||
Can't open Excel2003 workbook containing VBA macros with Excel2000. | Excel Programming |