disabling macros when closing WB

**orbatrin** · 01-02-2010, 02:13 AM

I am working on a small project that is complete but when i close the workbook and open a new workbook, my paste special/values macro that overrides the paste in the right click menu is still active. How do I disable all macros automatically upon closing workbook or how to disable specific macros auto.
here is the code for the paste special/values used on auto open and also a module.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This is what I have in ThisWorkBook to disable/enable cut, delete and cellDragDrop.

Please Login or Register  to view this content.

Thanks for the help.

**davesexcel** · 01-02-2010, 10:13 AM

I would suppose you would need a workbook_close event that resets everything when you close the workbook.

**royUK** · 01-02-2010, 10:19 AM

You really should not mess with user settings with your code, it can lead to many problems as you are beginning to find out.

As Dave says try putting your restore code into the workbook close event as well as de-activate

**orbatrin** · 01-02-2010, 01:59 PM

Thank you for your response. I am aware that changing user settings could lead to problems but this is the only way I can make it user friendly.

Is there a code to reset all to default? or can someone walk me through how to reverse my paste special code so that I may add to my deactivate sub or close event. All the code is listed.

**royUK** · 01-02-2010, 02:12 PM

Originally Posted by orbatrin

Thank you for your response. I am aware that changing user settings could lead to problems but this is the only way I can make it user friendly.

How does changing excelmake it more user friendly. You really should not do this unless your Excel VBA skills are above basic.

This example contains allthe code to remove cut,copy & paste & restore it

**wotadude** · 01-05-2010, 04:10 PM

Hi RoyUK,

Is it possible to re-assign paste functionality/shortcut/key to pastespecial xlvalues.
I often have a problem where users paste in data, and corrupt cell formatting and validation.

Any help would be appreciated.
Thanks.

**orbatrin** · 01-05-2010, 10:13 PM

royUK,

Thank you for the sample code that you posted, gives me an idea of more possibilities to activate and deactivate macros. Unfortunately the code does not look like it will override my paste special macros. that code is good to disable and enable cut, copy, and paste.

Just an idea...is it possible to reassign I.D's with a different control I.D and then reverse it at Workbook_Deactivate?? this would be a more simplified way to reassign Paste with Paste Special/values.

WotaDude is correct. Users are cutting/copying formulas and corrupting data..there has to be a more simplified way to override controls in specified workbooks. Any help would be greatly appreciated.

**orbatrin** · 01-06-2010, 09:04 PM

I figured it out...Thanks to Davesexcel and royUK

here is the solution to my original problem if anybody needs to know..

Please Login or Register  to view this content.

Originally I didn't think resetting the application.commandbars would call off the controls.OnAction = "PastValues" which is in my private module to do a Paste Special/Values by clicking on Paste in the right click menu. I also changed my Auto_Open to Workbook_Open as it was not compatible with my version of excel. Everything works and I'm happy.

If anybody sees a problem to my solution, please let me know.

Thanks for the help.