Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel 2010 - VBA code to disable undo command for a workbook

Posted on 2013-05-28
11
Medium Priority
?
3,329 Views
Last Modified: 2013-05-30
I am using excel 2010 and I need to find a way to disable the undo command when I open a certain workbook.  Everything I am able to find right now is for earlier versions of excel and I read somewhere and learned from my own attempts that I need a completely different approach for this in excel 2010.  I was thinking of disabling / enabling the command based on the workbook's activate / deactivate events.

Thanks for your help.
0
Comment
Question by:Matt Johnson
  • 6
  • 5
11 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39201631
Hi,

Are you able to update the Windows Registry within the run-time environment?

Although not stated explicitly within the following article, you can adjust the number of undo available within the Workbook_Open() event code, & restore within the Workbook_BeforeClose() event code, or the Activate/Deactive events as you suggested:

"How to modify the number of undo levels in Excel"
[ http://support.microsoft.com/?kbid=211922 ]

Simply use the "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options" key for Microsoft Excel 2010.

---
By default, Excel allocates RAM for 16 undo levels. The registry setting that is described in this article lets you change the default value to a value that you specify.

If the undo history is set to zero, no memory is allocated for undoing actions. The higher the undo history value, the more memory Excel allocates for the history. This directly affects the performance of your computer when you run Excel.

Note When you run a Visual Basic for Applications macro, Excel allocates no memory for undoing actions. This feature is disabled for optimization of performance when you run a macro.

To use Registry Editor to change the number of undo levels, follow these steps.

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756 How to back up and restore the registry in Windows

    Close any programs that are running.
    Click Start, click Run, type regedit in the Open box, and then click OK.
    In Registry Editor, expand one of the following registry subkeys, as appropriate for the version of Excel that you are running:

    Microsoft Office Excel 2007
    HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options
    Microsoft Office Excel 2003
    HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options
    Microsoft Excel 2002
    HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options
    Microsoft Excel 2000
    HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options
    On the Edit menu, point to New, and then click DWORD Value. Select New Value #1, type UndoHistory, and then press ENTER.
    On the Edit menu, click Modify.
    In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 100 in the Value box, click OK, and then exit Registry Editor.
    Start Excel. Excel stores an undo history for the number of actions that you specified in step 6.
---

BFN,

fp.
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39201651
You can start here (for enabling/disabling hot keys and toolbars), maybe it helps:

http://www.rondebruin.nl/win/s4/win011.htm

http://www.rondebruin.nl/win/s5/win005.htm

http://www.rondebruin.nl/win/section6.htm
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39201654
you can adjust the number of undo available
Please read as...
you can adjust the number of undo levels available
:)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39201661
By the way: IT'S A VERY VERY BAD IDEA TO TOUCH THE REGISTRY IN ORDER TO GET THINGS DONE ;-)
It's just a messy workaround :-( = (very) bad paractice
0
 
LVL 14

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 2000 total points
ID: 39201679
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39201684
By the way: IT'S A VERY VERY BAD IDEA TO TOUCH THE REGISTRY IN ORDER TO GET THINGS DONE ;-)
It's just a messy workaround :-( = (very) bad paractice

It is not "messy" at all.  It is the most efficient method of meeting your needs.

The configurable settings are there for the purpose of changing them to suit your environment's requirements.

If you are careful, & follow the advice/instructions provided by Microsoft, then there is minimal risk.
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39201985
I have NEVER seen anyone writing efficient AND good code (in terms of SW design and SW patterns) at the same time through manipulating the registry...
Nevertheless, in the end it's up to mattej1 to choose the "right" solution which suites him best ;-)
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39202275
I have NEVER seen anyone writing efficient AND good code (in terms of SW design and SW patterns) at the same time through manipulating the registry...

You're new here.
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39202432
You're new here.

So what?!
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39202618
So what?!
If you were not a new member, then you may have seen some of my previous contributions that use the registry settings for tasks that are far from easy (if not impossible) using coded solutions.

For example, "URGENT- Auto enabling the Macros" (October 2003)

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_20753813.html ]

Also, "Importing Excel Spreadsheet into Access 20[0]3" (October 2005)

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21598125.html#a15160522 ]

Registry settings are there to be used, otherwise why would they be there at all?

I am sorry you are not as familiar with their usage & their possibilities, but that does not make them inefficient or detrimental to good coding techniques.  They can co-exist, &/or complement such techniques.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39208273
You're welcome,  mattej1.

(Sigh)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

886 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