Turning off macros in XL 2010 temporarily

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.
LVL 17
krakatoaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Luis Clara FernandesIAM CoordinatorCommented:
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
Rob HensonFinance AnalystCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Randy PooleCommented:
I would go with Phillip Burton's answer, a global variable which is set and unset via a macro on a command button.
0
krakatoaAuthor Commented:
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
krakatoaAuthor Commented:
@ 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
Rob HensonFinance AnalystCommented:
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
Rory ArchibaldCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
krakatoaAuthor Commented:
OK so the enableevents + ribbon way looks the best. Thanks.
0
Martin LissOlder than dirtCommented:
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
krakatoaAuthor Commented:
Thanks. Yes, sorry, all the cake's gone. ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.