Turn Off Macros With Workbook Open

[Guest]

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]

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.

 

[Sharad Naik]

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

 

[Sharad Naik]

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

 

[Guest]

Thanks for this, is there code to turn off the VB toolbar?
Thanks
Paul Moles

:

 

[Sharad Naik]

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

 

[Sharad Naik]

Sorry it should be = False in the last code line in my below post.
Sharad

 

[Guest]

Thanks for this.

Where / how do I use
Application.EnableCancelKey - xlDisabled

Many Thanks

 

[Sharad Naik]

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