Disable then Enable Drag & Drop - on Open and Close...

[poolio]

This should be an easy one but I'm struggling to enable Drag & Drop when I close down my Workbook, I can disable when I open with:

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub

But when I close it down... this doesn't seem to work:

Private Sub Workbook_Close()
Application.CellDragAndDrop = True
End Sub

In fact it leaves it disabled for any workbook I subsequently use....

Should I be saving them both into the This Workbook in VB, or into a Module (what's the difference #blush#)

Thanks
Jim

 

[VoG]

Try this instead of the second one. Both go in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.CellDragAndDrop = True
End Sub

 

[poolio]

Try this instead of the second one. Both go in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.CellDragAndDrop = True
End Sub

VoG,
Thanks for the reply, it's not bad, as it re-enables D&D on any save; so assuming people saved just before they exit it would be perfect. However if someone were extensivly inputting into the sheet and wanted to do a quick [Ctrl+S] mid modification, then the D&D would be reactivated = danger! So I really ONLY need it to be re-activated during the, Exit process... is there an alternative to: Workbook_Close() ?

 

[VoG]

How about this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
Me.Save
End Sub

 

[poolio]

Mr VoG... sir, you are a gosh darned Genius!

As the yoof might say 'nuff respeck guy'!

How about this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
Me.Save
End Sub