0

I have been using a macro on an excel document that zooms in charts present in any sheet. The problem is, if I want to edit a chart, I must click it, triggering the zoom.

For this particular reason, I need to disable macros, and was wondering if there is either an easy to reach button or a keyboard shortcut to toggle it.

ravemir
  • 1,153
  • 2
  • 13
  • 29
  • Is the macro triggered by opening a sheet, or is there some other activity triggering it? – Jüri Ruut Dec 10 '12 at 19:40
  • I'm not entirely familiar with how the macro is implemented, but the macro is triggered while click any chart within a sheet. It was found [here](http://excelexperts.com/Chart-Zoomer) – ravemir Dec 10 '12 at 19:44
  • 5
    In the XL file, press `Alt+F11`. Go to the `ThisWorkbook` module and comment out the two lines inside both subs. Save and close the file, then reopen. You can then edit to your hearts content. To put the zoomer back on, uncomment the lines. You may need to Save, Close and Reopen again. – Scott Holtzman Dec 10 '12 at 20:02
  • 2
    @ScottHoltzman - pls post the comment above as the answer so you can get the appropriate credit – brettdj Dec 11 '12 at 04:03
  • That's quite far from effective, what's probably going to happen is I am going to be disabling them forever and the macros just laying there. But I guess it's better than deleting and re-copying the macros every time. – ravemir Dec 11 '12 at 10:54
  • Well, since I found a way to have shortcuts [here](http://www.vbrad.com/article.aspx?id=89) to commenting and uncommenting inside the VBA editor, and with the fact that I don't actually need to close the Excel file, I guess this can be swift enough to be efficient. Add your comment as an answer, along with the link I provided (or not, I can edit it later) and I'll give credit :) – ravemir Dec 11 '12 at 10:56
  • 1
    A slight addedum: uncommenting the code and saving won't completely work right away, but you still don't need to re-open the whole workbook: just change the sheet and it will start working (Ctrl+Pg up/dn) – ravemir Dec 11 '12 at 11:12
  • 1
    Answer added. Thanks also for the comments to help deepen the answer. – Scott Holtzman Dec 11 '12 at 13:46

3 Answers3

3

In the XL file:

1) Press Alt+F11

2) Go to the ThisWorkbook module and comment out the two lines inside both subs.

3) Save and close the file, then reopen.

4) You can then edit to your hearts content.

5) To put the zoomer back on, uncomment the lines. You may need to Save, Close and Reopen again

NB -> see comments on original post for more information.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
3

If you'd like a little more control without having to constantly comment/uncomment the macro, you might add something like this to the macro. Make sure you put it at the top, before any of the other code:

Dim mb As VbMsgBoxResult    'Declare a variable to hold the messagebox result
'Prompt the user:
mb = MsgBox("Would you like to edit this chart?", vbYesNo, "Edit Chart?")
If mb = vbYes Then
    Exit Sub   'If user wants to edit the chart, then exit this subroutine
    'a response of "No" will allow the sub to continue uninterrupted
End If

Without seeing exactly how the macro is being called, this might require some tweaking, but it's essentially just a prompt asking whether you want the macro (zoom) to continue, or whether you want it to terminate, allowing you to access the chart object for editing.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • That's a good point, but even though your option requires less steps than commenting, I'm afraid that having to do that each time I want to edit/zoom a graph will quickly add up and become cumbersome. Maybe a prompt to edit a toggle variable would work, but I'm currently happy with this approach :) – ravemir Dec 11 '12 at 15:22
  • @ravemir You're correct, obviously you could fine tune this approach. Practically speaking, I would add the msgbox prompt to the Workbook_Open event, and then pass the result to the zoom macro, adding one line of code like `If mb = vbYes Then Exit Sub`. This would let you establish whether you want to edit the chart(s) only once, when you open the file, and would ignore the 'zoom' procedure thereafter. – David Zemens Dec 11 '12 at 19:36
  • 1
    I was thinking more in the lines of using a shortcut to trigger the prompt, which edits a global variable. That would be ideal, since Iam going to be adding alot of worksheets to the book, and zooming and editing its charts inbetween. – ravemir Dec 11 '12 at 22:59
  • Yep, there's more than one way to skin this cat, and the best solution for you will be the one that best accounts for *how* you use the file. – David Zemens Dec 12 '12 at 02:35
1

As of Excel 2010* there's actually an option on the "Developer" tab which allows you to disable macros as easy as ABC. Design Mode lets you do just that!

*Maybe even earlier versions.

Panos Gr
  • 667
  • 5
  • 13