VBA Enable cell-drag-and-drop?

[merlin_the_magician]

I have a worksheet in which i disabled CutCopyMode and CellDragAndDrop functions through VBA in order to prevent users from messing up the sheet.
Code is simple and looks like this:

With Application
.CutCopyMode = False
.CellDragAndDrop = False
End With

Works lovely, unfortunately just a little to much... after closing the workbook the functions remain disabled.
How can I re-enable these functions on exit of the workbook, and where shoud i put the code?



With Application
.CutCopyMode = False
.CellDragAndDrop = False
End With

 

[Ivan F Moala]

Put the Enable code in the Workbook before close Event

 

[merlin_the_magician]

Ivan... dumb question how am i to write this code? I have been trying, but i don't know how to call the before close procedure

 

[lpvdsteen]

in 'this workbook'

Private Sub workbook_BeforeClose(Cancel As Boolean)
With Application
.CutCopyMode = True
.CellDragAndDrop = True
End With
End Sub

 

[lpvdsteen]

Mind,

the application cutcopymode only cancel a selection. I does not prevent the user from selecting and copying. You can prevent this by the following in this workbook:


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

This will as far as I can see prevent the user from pasting the cells all over the place. ( I have had the same problem with user making a mess of a very nice layout by copying and pasting)
Luke

 

[merlin_the_magician]

Thank you for the code Luke. I have been messing around with the "Private Sub workbook_BeforeClose(Cancel As Boolean)".

As for the second script, for the moment the first method seems satisfy my security needs.

In the workbook, there really is not that much to copy at all. The initial idea was to prevent users from applying the DragAndDrop option that will allow them to "auto-fill" a range. When they do, numerous validations and conditional formats may be removed or altered causing severe damage to the worksheet.