Disable "Cut" and "Drag & Drop" but not copy

C

CraigKer

I used code from Jim Rech below to disable cut and past as well as drag and
drop so my formula's don't get messed up when some one does a cut/past to a
data cell. However, the .CellDragAndDrop=false command not only stops drag
and drop but also stops drag and fill (drag and copy). I want to keep that
functionality. Is there a way to stop drag and drop without stopping drag
and fill/copy?

Here is the code i'm using to do all of this:

Sub CutsOff()
AllowCuts False
End Sub

Sub CutsOn()
AllowCuts True
End Sub

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
''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}"
Else
.OnKey "^x", ""
.OnKey "+{Del}", ""
End If
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top