Clark asks:
I am trying to copy two different operations into a "This workbook". One is the "disable cut copy paste" function that I found and the other is the "force user to enable macros" so that the disable cut copy paste function will automatically run. However, I get an error message when I put in both of these functions. How can I run two different functions simultaneously in the "This workbook" area? I hope I have explained this sufficiently. Thanks!
The articles we're referring to are:
- Disable Cut, Copy, Paste: http://www.vbaexpress.com/kb/getarticle.php?kb_id=373
- Force User To Enable Macros: http://www.excelguru.ca/node/91
Now, for users who are still learning, this can be a bit tricky. A couple of really important points to know when combining entries:
- There can only be one "Option Explicit" line, and it must be at the top of the module. (It can be below other "Option" lines, but to be safe just make it the first line.)
- There can only be one procedure in the module with any given name. I.e. you can't have two Workbook_Open routines.
So what that means is that we need to stitch our workbook_open code together. In this case the routine from "Force Users To Enable Macros" is longer, so we'll start with that one. The code from "Disable, Cut, Copy, Paste" is only one line, so we'll cut and paste that into the routine as follows:
Private Sub Workbook_Open()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Unhide all worksheets since macros are enabled\Application.ScreenUpdating = False
Call ToggleCutCopyAndPaste(False)
Call ShowAllSheets
Application.ScreenUpdating = True
ThisWorkbook.Saved = True
End Sub
And that should be it. None of the other routines have conflicting names so, providing they are all placed in the appropriate modules things should work.
9 thoughts on “Force Macros and Disabling Cut, Copy, Paste”
Hi Ken,
On the issue on forcing users to enable macros, I've been using a few different techniques for some time. In an effort to find a better approach, I recently came across a couple of your posts on the subject. I have found issues with each technique. The most recent post appears to be:
http://www.excelguru.ca/node/91
However, there are two issues; 1) It is easy to end up saving a file with no extension when the user clicks Save As, 2) You end up with an event loop if the user clicks the file close button and you click Yes to save. The dialog goes away if you eventually select No or Cancel, but how is an inexperienced user going to know for sure that his/her file was actually saved? Putting If Me.Saved (or ThisWorkbook.Saved)=True Then Exit Sub at the top of the module at least displays the close dialog only twice if you click Yes to save.
The other article:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=379
addresses the event loop issue, but creates two new problems of its own: 1) If the file with your code is the last one and you close via the application close button, the application doesn't close after saving - you have to click the close button again to exit. This is a minor issue that you mention in the article, 2) If the file with your code isn't the last file, when you close the file, you have no UI to work with other open files (UI functions no longer work). This is a major issue that you **don't** mention.
In examining the code, I was struck by the following block in BeforeClose:
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Huh? Since BeforeClose runs code before closing, why is there a need for explicitly using .Close savechanges:=False? Since events are turned on before this line is executed, BeforeClose gets called again. Anyway, removing this line corrects both problems mentioned. With a few other mods, this code is now my preferred method to force users to enable macros, since it covers all possible scenarios and works exactly the way God intended. Thanks! 😀
Hi Ken,
Thank you very much for the info on you blog.
I teach at a university and your code would be very helpful to design on-line homework assignments but was faced with a problem when I added IRM to get the Excel file to expire after a due date. I combined the codes for "Force Macros and Disabling Cut, Copy, Paste" and it worked great, but after adding IRM, it only worked with me (I have "full control"); it did not work for the users who have "Change" access level in IRM.
Any advise would be much appreciated.
Thanks
Aziz
The above code for disabling cutcopypaste works in as much as it disables the right click function, but how to you disable this Macro? Assitance would be appreciated!
Hi there,
Go into the VBE and run the following from the Immediate window:
ToggleCutCopyAndPaste(True)
Then remove the ToggleCutCopyAndPaste line from the Workbook_Open macro and you should be set.
I have found the ?Disable Cut, Copy, Paste macro to be very useful but it only works for when a user is using keyboard short cuts. I find that users are still able to use the right click cut/copy/paste as well as the buttons on the ribbon. I am familiar with VBE but I am not an expert. Any help in disabling these two functions would be much appreciated.
Thank You!
That's odd, Corey. If the routine has been pasted into all the right modules as per the VBAExpress article, it should be treating the right click menus consistently with the keyboard shortcuts...
How can I block copy paste my graphs? This codes only works for cells. Even short cuts are not working on the graph, but when I right click then I can copy it and then I can paste it into another sheet or place.
Removing ".Close savechanges:=False" line solved the problem.
Thank you Colin Banfield
Cool, that was a quick answer! Thanks for posting back. 🙂