VBA to disable Cut and Paste options within the Home ribbon AND the right-click menu?

[AGrayson84]

Hello everyone! So I'm running into a few roadblocks trying to prevent people from pasting anything other than "values" only, as well as preventing people from using the Cut option. I have my worksheet locked, but multiple people are copying cells with various formatting (ie: cell background colors/fills), and the formatting is carrying over to my workbook and it is over-riding my conditional formatting. I've spent yesterday afternoon and this morning surfing the Internet for ways to prevent cutting and pasting, and so far I have found several macros out there, but none appear to stop any "work-arounds" for people that are trying to cut or use any other paste methods. At this point, I have successfully prevented the ability to do the CTRL + V and CTRL + X key combinations to prevent cutting and pasting that way, left-clicking a cell now automatically pastes the value only of anything on the clipboard, and it appears that the Cut method is now disabled when right-clicking on a cell with a value..... but I can still right-click a cell and choose any paste method I want, and the Cut and various Paste options are still available and functional from the Home ribbon.

I can't seem to find anything out there that successfully prevents the Cut and the Paste options from the right-click menu as well as the Home ribbon. Is anyone able to assist with this please? Training the various people using this spreadsheet to be sure to not use any other paste options is unfortunately out of the question, due to the over-whelming volume of people using my workbook.

Here is the code I'm currently using so far:

In a standard module:

Please Login or Register  to view this content.

In "ThisWorkbook":

Please Login or Register  to view this content.

Any solid help would be really appreciated, as the various disabling of the "CommandBars" does not seem to be successfully disabling things everywhere. Thanks so much!

[Bernie Deitrick]

This should work all by itself - insert this into the ThisWorkbook object's code module:

Please Login or Register  to view this content.

[AGrayson84]

Hi Bernie, I appreciate the help! I applied your suggestion to a new workbook and see where it does offer some assistance with people left-clicking and right-clicking to paste clipboard data, and I think I may use that instead of a portion of code that I already have since mine didn't already prevent paste options when right-clicking, but any idea on how to completely prevent the option to Cut from the right-click menu and the Home ribbon, and how to prevent the option to Paste from the Home ribbon?? Thanks again!!

[Bernie Deitrick]

I don't like to mess with user's ribbons - that's a recipe for disaster. Just use your workbook, and prevent things within your workbook. Setting CutCopyMode to False will prevent any cutting from completing - use the worksheet activate event, the workbook deactivate event, etc. If you set the cutcopymode to false, then the paste options get greyed out because the clipboard is emptied when you use that, effectively 'removing' cut and paste as an option. You can also start to train your users, by checking for cut mode and giving them a message - "Hey - no cutting allowed in this workbook!"

[AGrayson84]

Definitely understand being hesitant to alter the ribbons. The two reasons I'm wanting to do it though is because the above code doesn't prevent someone from cutting from my workbook and pasting in another workbook; and since there is a constantly-changing group of people from another division using this file it's going to be impossible to train them all, when I don't even know who the people are. So I basically merged your solution with this other one I just found this morning, and it seems to solve all of my problems...

ThisWorkbook:

Please Login or Register  to view this content.

Thanks again for the great help!!

[Bernie Deitrick]

You could just add in the workbook_deactivate event and that will prevent them from cutting from yours into another.

[AGrayson84]

You could just add in the workbook_deactivate event and that will prevent them from cutting from yours into another.

Awesome! Thanks a ton, just did that and it did the trick!

Only other thing I'm hoping you can maybe help with.....

So I shot myself in the foot with this whole plan, for one small portion of VBA that I have that is commanded by a macro. I was assuming/hoping that since the pasting on this portion I'm speaking of was going to be done in VBA (versus from the user interface), that it would not be affected by the macro..... but I'm finding today that I was wrong.

Here is the snippet of code that is getting caught up by the macro of yours that I'm using on the module for Sheet5:

Please Login or Register  to view this content.

I'm been struggling all morning to implement a way around it, but not being educated enough with how VBA within "ThisWorkbook" operates, my attempts at bypassing your VBA for my snippet above have failed. Is there any way you can think of that I can tell your VBA to not throw the MsgBox prompt when this other line of VBA in Sheet5 is called, and just allow it to paste the values like it normally would prior to me implementing your code?

Thanks so much once again!!!!!!!!!!!!!!

[Bernie Deitrick]

Any time you have event code and macros that manipulate the workbook, you need to use code like this

Please Login or Register  to view this content.

Depending on other factors - calculation modes, etc, I often use code like this to minimize the effect of the macro on the performance of Excel:

Please Login or Register  to view this content.

[AGrayson84]

Bernie!! Thank you sooooo much!!! The first, simpler one worked like a charm! I really appreciate everything!!!

[Jenny S]

This is fantastic. I have made a small adjustment.

Please Login or Register  to view this content.

This allows multiple pastes of values from one copy and empties the clipboard if it's a cut.