Solved

Turning off macros in XL 2010 temporarily

Posted on 2014-09-19
11
179 Views
Last Modified: 2014-09-19
Hi -

I seem to be having trouble finding a way of disarming macros - vba - temporarily, so that the user can input and edit data in one of the sheets, without that editing causing events and routines to fire. Likewise, I'd like to know how one manually allows the user to switch macros back on once the editing has been done and the macros are required. Thanks.
0
Comment
Question by:krakatoa
11 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40332312
Why not have a button they can press, which sets a variable, so that, the events do still fire, but they first test whether the variable has been set and then not proceed any further?

This way, you will have the best of both worlds - flexibility and control.
0
 
LVL 5

Expert Comment

by:Luis Carlos Clara Fernandes
ID: 40332314
You can deactive macros on the user computer as you can see in attached picture or you can save the file without macros.
macro-security.jpg
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40332316
AFAIK there is no way to switch off Macros once they have been enabled for a particular file or Excel session.

I would possibly get round it by having a variable in the workbook that the Routine checks first as to whether the routine should Run.

For Example, in the Workbook there is a cell (lets say Sheet1,G1) that has the option of TRUE / FALSE and the first line of each VBA Routine would read:

CheckVal = Range("sheet1!G1")  (Can't recall proper syntax for referring to Sheet & Cell)
If CheckVal = FALSE Then Exit Sub

if these are WorksheetChange event macros that are running, they will effectively still run but when the check finds FALSE they will not do anything. As always, when VBA runs, whether doing anything or not, the Undo History will be cleared.

Thanks
Rob H
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 21

Expert Comment

by:Randy Poole
ID: 40332327
I would go with Phillip Burton's answer, a global variable which is set and unset via a macro on a command button.
0
 
LVL 16

Author Comment

by:krakatoa
ID: 40332377
I thought that in previous versions of Excel that it *was* possible for the user to choose to effectively toggle macros on and off. Although this is now an academic point, in terms of this question, did that not used to be the case?

What I am not over the moon about, is having to show the user an interface widget - button, whatever - at all when the workbook is first opened. My real problem is that *if* the user wants to do some copy and paste operations on the sheet which contains the OnRightClick macro, they will not be able to complete that operation (say for copying in some large range of cells from another worksheet for example), because - for reasons to do with the programme design - that onrightclick event has to be cancelled at the end of the call, and so a Paste won't work.

It's difficult to believe that there is not an open option for the user to manage macro involvement.
0
 
LVL 16

Author Comment

by:krakatoa
ID: 40332462
@ Luis Carlos Clara Fernandes

Well I have obviously tried that already, but changing the macro enablement settings does not work. I go to the Developer menu, and make the choice as per your graphic; then save the Workbook, but when it is reopened the macros are still operational.
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 150 total points
ID: 40332536
For that one example, Copying and Pasting, I would suggest users use the Ctrl C & Ctrl V options rather than Right Click.

Unless of course you have assigned those keyboard shortcuts to something else??

Thanks
Rob
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 350 total points
ID: 40332569
You can disable macros for a workbook but then you have to reopen it to re-enable macros. The alternative, since you seem to be only dealing with event code is to have simple routines to enable/disable them:
Sub EnableCode()
Application.Enableevents = True
End Sub

Sub DisableCode()
Application.Enableevents = False
End Sub

Open in new window


and assign those to buttons on the ribbon.
0
 
LVL 16

Author Closing Comment

by:krakatoa
ID: 40332799
OK so the enableevents + ribbon way looks the best. Thanks.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40333016
Not expecting any points but you could have just one macro.

Sub Maintenance()
Application.EnableEvents = Not Application.EnableEvents
If Application.EnableEvents Then
    MsgBox "Events Enabled"
Else
    MsgBox "Events Disabled"
End If
End Sub

Open in new window

0
 
LVL 16

Author Comment

by:krakatoa
ID: 40333081
Thanks. Yes, sorry, all the cake's gone. ;)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question