VBA Block Drag and Drop

cityanangelstx2004

Board Regular
Joined
Jun 18, 2004
Messages
56
Does anyone know how to prevent a user from using the drag and drop feature using VBA? I know the user can turn this feature on and off using Tools | Options | Edit feature, but I am protecting a worksheet and I would really like to prevent someone from accidently dragging and dropping cells. :(

Thank you for your help in advance.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

in the sheetmodule you could put this
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Application.CellDragAndDrop = True Then
    Application.CellDragAndDrop = False
    MsgBox "The feature """" Cell Drag and Drop """" is disabled", 64, "Drag & Drop"
    End If

End Sub
as a respect to my users, I reset the settings
therefore
in normal module
Code:
Global DragDrop As Boolean
in workbookmodule
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = DragDrop
End Sub

Private Sub Workbook_Open()
DragDrop = Application.CellDragAndDrop
End Sub
if you want to apply this to all your sheets, then put the first code in the workbookmodule
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'same code
End Sub
kind regards,
Erik
 

cityanangelstx2004

Board Regular
Joined
Jun 18, 2004
Messages
56
Thank you for your reply. I didn't consider the fact that since I was disabling the drag and drop feature I should reset it before closing the workbook. Thank you for the thorough explanation. :biggrin:
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you're welcome :)
I should reset it before closing the workbook.
where "reset" means: "change to state before you opened the workbook"
if it was enabled, it will be enabled again
if it was disabled, it will stay disabled
 

Forum statistics

Threads
1,137,298
Messages
5,680,695
Members
419,927
Latest member
Axtros

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top