Solved

Turning off macros in XL 2010 temporarily

Posted on 2014-09-19
11
176 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 31

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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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 31

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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now