Prevent Only CUT and PASTE from the user Not COPY

[kukerjoe]

Hi,
i want to prevent the user from doing Cut and Paste but to allow him to preform Copy and paste ( when they are doing Cut and Paste their getting Ref# error in formulas)
when i use this code it prevents also the Copy and Paste which is needed by user and it doesn't do the Ref error

application.CutCopyMode=False

Thanks!

 

[bugmonsta]

Hello,

I have this in one of my spreadsheets and it works quite nicely, this is set on my sheet and tells the user not to use cut and paste (no idea where this code came from as I forgot to put a link in)

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Select Case Application.CutCopyMode
    Case Is = False
'do nothing
    Case Is = xlCopy
'do nothing
    Case Is = xlCut
    MsgBox "Please DO NOT Cut and Paste!"
    Application.CutCopyMode = False 'clear clipboard and cancel cut
End Select

End Sub

You can also stop the user pressing Ctrl X via this little bit of code, it reactivates when they come off this sheet

Private Sub Worksheet_Activate()
Application.OnKey "^x", ""
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "^x"
End Sub

then there is this code which will remove the cut option from the menu (right click cut will be greyed out) - this code is from ozgrid (remembered the link when I found this one!!). The message above will also capture this one, however I think its nice to show the user they don't have the option as well!

Sub testme()
'Disable all Cut menus

For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = False
Next oCtrl
'Disable all Copy menus

End Sub

to active the menu again:

Sub active()

Dim oCtrl As Office.CommandBarControl

'Enable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl
'Enable all Copy menus

Application.CellDragAndDrop = True

End Sub

 

[kukerjoe]

WOW!
Beautiful! thank you!