How do I disable the right mouse cut and paste facility?

[DexonII]

I need to be able to prevent any user from cutting and pasting data in one of my spreadsheets. I have protected it all, removed cut and paste from the options- edit tab, but you can still cut and paste with the right mouse shortcut key.

How do I stop this from being available?

Paul.

 

[VoG]

I can't reproduce the error, sorry. Try running the code and when you get the error click the Debug button and see which line of code is highlighted. Then post all of the cut/paste disabling code here and indicate the faulting line.

 

[DexonII]

Hi VoG,

Here is the code as I am using it. It needs to be debugged as shown:

Sub Disable_cut_copy_paste()

Application.OnKey "^{c}", "" 'Copy
Application.OnKey "^{v}", "" 'Paste
Application.OnKey "^{x}", "" 'Cut

ShortcutMenus(xlWorksheetCell).MenuItems("Cut").Delete 'needs debugging on this line

ShortcutMenus(xlWorksheetCell).MenuItems("Copy").Delete
ShortcutMenus(xlWorksheetCell).MenuItems("Paste").Delete
ShortcutMenus(xlWorksheetCell).MenuItems("PasteSpecial").Delete
End Sub

This sub is called when the workbook is opened.

Any ideas?

 

[VoG]

Try this

Sub Disable_cut_copy_paste()

Application.OnKey "^{c}", "" 'Copy
Application.OnKey "^{v}", "" 'Paste
Application.OnKey "^{x}", "" 'Cut

On Error Resume Next
ShortcutMenus(xlWorksheetCell).MenuItems("Cut").Delete
ShortcutMenus(xlWorksheetCell).MenuItems("Copy").Delete
ShortcutMenus(xlWorksheetCell).MenuItems("Paste").Delete
ShortcutMenus(xlWorksheetCell).MenuItems("PasteSpecial").Delete
On Error GoTo 0
End Sub

 

[DexonII]

That sort of works - the cut copy and paste have all disappeared, but the paste special option is now available and it pastes the last copy I performed.

For exemple the last thing I copied was a sub routine to display a message box and the code for this is pasted special when I use the right mouse option.

How do i solve this? Is there some way to "clear" the paste special memory?

 

[VoG]

Try

Application.CutCopyMode = False

 

[DexonII]

Where do I place it? After the On error line, or somewhere else?

 

[VoG]

I would put it as the first statement in the sub.

 

[DexonII]

That doesn't appear to do it.

I can now paste Application.CutCopyMode = False everywhere cos it was the last thing i copied.

Am I not specifying the paste special correctly to disable the shortcut menu item?

 

[VoG]

This works for me:

Sub Disable_cut_copy_paste()
Application.CutCopyMode = False
Application.OnKey "^{c}", "" 'Copy
Application.OnKey "^{v}", "" 'Paste
Application.OnKey "^{x}", "" 'Cut
On Error Resume Next
ShortcutMenus(xlWorksheetCell).MenuItems("Cut").Delete
ShortcutMenus(xlWorksheetCell).MenuItems("Copy").Delete
ShortcutMenus(xlWorksheetCell).MenuItems("Paste").Delete
ShortcutMenus(xlWorksheetCell).MenuItems("Paste Special...").Delete 'Note space and ...
On Error GoTo 0
End Sub

Note that I recoded the bit for PasteSpecial - getting the exact name of a menu item is important.

 

[DexonII]

That works a treat!!

Its always the obvious that gets missed!

Thank you for helping me out.

Can i be so bold as to ask one more question?

Is it possible to disable the cut, copy, paste and paste special from the drop down menus at the top, just for this spreadsheet?

Paul