Turning off macros in XL 2010 temporarily

Posted on 2014-09-19
Medium Priority
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.
Question by:krakatoa
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.

Expert Comment

by:Luis 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.
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.

Rob H
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
LVL 16

Author Comment

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.
LVL 16

Author Comment

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.
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 600 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??

LVL 85

Accepted Solution

Rory Archibald earned 1400 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.
LVL 16

Author Closing Comment

ID: 40332799
OK so the enableevents + ribbon way looks the best. Thanks.
LVL 49

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"
    MsgBox "Events Disabled"
End If
End Sub

Open in new window

LVL 16

Author Comment

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

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

765 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