Solved

Turning off macros in XL 2010 temporarily

Posted on 2014-09-19
11
183 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
[X]
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
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 47

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

726 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