How to disable Excel sheet drag-drop to another excel workbook?

Question

I have developed VSTO add-in for Excel 2010. I want to disable the functionality of dragging of an excel sheet to another workbook.

I tried following Application events for some workaround :

Application_WorkbookActivate

Application_SheetActivate

Application_SheetChange

Application_WorkbookNewSheet

I used these functions for capturing an event when one sheet gets removed from one workbook & created into another workbook. So I was trying in this way to disable the dragging functionality.

I tried one property as well Application.CellDragAndDrop. But it's only for to disable a cell of a sheet.

I tried some scenarios using ribbon as well but i didn't find any success.

So can anyone tell me how can I disable dragging of an Excel sheet to another workbook.

I tried it in VBA from your 1st question, and SheetChange got me the event, but not the origin sheet name. I'd look into your addin controlling it, so it looks at the excel sheets open, and has an origin sheet variable, so if the mouse button is down and the sheet name changes a PerfomringDragDrop variable is set, then the sheetChange event should alert its a drag drop based on this var. So on the Application WorkbookActivate in the addin, you need to track the workbook name. — Nathan_Sav
Hi Nathan, I think your suggestion is for cell dragDrop. But I want to disable dragging of whole worksheet to another workbook. — Ajit Medhekar
Hi, your approach would still remain the same, if the active workbook changes, but the mouse key is down, then say "drag drop of sheets not allowed". — Nathan_Sav
ohoo cool idea !! But then in this scenario, I'm not able to find mouse down event in VSTO. And second thing, If we apply mouse down even & workbook activate/deactivate event, How can I cancel that drag event ? because that sheet already gets dragged. — Ajit Medhekar
I dont have VS on my work computer, but in the workbook deactivate event, i'd have a bool saying blnMouseDown and set this to be true, then in the workbook activate event if the bool was true, revert back to the original workbook. — Nathan_Sav

Ajit Medhekar Ajit Medhekar · Accepted Answer · 2016-01-20T16:50:06

I have written a workaround for this solution. I also followed Nathan's suggestion. Here is my sample code/logic.

      If (Not IsSheetExists) Then
                'bypass delete prompt
                Application.DisplayAlerts = False
                Sh.Copy(After:=OldActiveWorkbook.Worksheets(1))
                Sh.Delete()
                Application.DisplayAlerts = True
                MessageBox.Show("We have blocked Drag-Drop functionality for security reasons.")
      End If

Variables used in sample code :

IsSheetExists : Check if sheet which we are dragging is exist in new workbook.

OldActiveWorkbook : Workbook from where we dragging a sheet.

And I have written this code in SheetActivate event.

How to disable Excel sheet drag-drop to another excel workbook?

1 Answers