Disable "Cut&Paste" and "Drag&Drop"

[johnny]

I want to disable this two function when I open a workbook. I've tryed both
this code:
Private Sub Workbook_Open()
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

and

Private Sub WorkbookOpen(ByVal Wb As Workbook)
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub
but no one of them will work the right way. I've put it both in a module, in
"This workbook" and in the actuall sheet.
Anybody got an idea whats wrong?

 

[Ken Johnson]

This works by disabling the Menu commands and keyboard shortcuts.
I got the idea from code posted by Jim Rech which disabled Cut, Copy
and Cell Drag and Drop.
I extended it to also disable Paste and Paste Special.
It works between different Excel sessions so that something copied in
a different session cannot be pasted in the protected session.
The three Subs are Private so that they can only be run from within
the VBA Editor.
To turn Cut, Copy, Cell Drag and Drop, Paste and Paste Special... off
run CutsOff().
Run CutsOn() to turn them all back on.

Private Sub CutsOff()
AllowCuts False
End Sub

Private Sub CutsOn()
AllowCuts True
End Sub

Private Sub AllowCuts(bEnable As Boolean)
Dim oCtls As CommandBarControls, oCtl As CommandBarControl
Set oCtls = CommandBars.FindControls(ID:=21) 'Cut
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
Set oCtls = CommandBars.FindControls(ID:=19) 'Copy
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
Set oCtls = CommandBars.FindControls(ID:=6002) 'Paste button
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
Set oCtls = CommandBars.FindControls(ID:=22) 'Paste in Edit menu
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
Set oCtls = CommandBars.FindControls(ID:=755) 'Paste Special...
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
''Disable Tools, Options so D&D cannot be restored
Set oCtls = CommandBars.FindControls(ID:=522)
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
With Application
.CellDragAndDrop = bEnable
If bEnable Then
.OnKey "^x"
.OnKey "+{Del}"
.OnKey "^c"
.OnKey "^v"
Else
.OnKey "^x", ""
.OnKey "+{Del}", ""
.OnKey "^c", ""
.OnKey "^v", ""
End If

End With
End Sub

Special thanks to Jim Rech.

Ken Johnson

 

[Tom Hutchins]

I htink you are disabling cut/copy/drag temporarily, but it is getting
re-enabled by subsequent operations. Instead of the Workbook_Open event, use
the Workbook_SheetSelectionChange event. Every time any cell is selected,
cut/copy/drag is disabled. This event code needs to be in the ThisWorkbook
module:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Application.CutCopyMode = False
Application.CellDragAndDrop = False
End Sub

Hope this helps,

Hutch

 

[johnny]

This was exatly what I needed. Thank you Tom! It works perfectly.

Tom Hutchins skrev: