My undo routine fails with drag and drop.

[serdar]

I want to block user to make changes in multiple cells at a time (at certain
circumstances but disregard it now).

I have this code (in the change event procedure) to undo any changes occurs
with multiple cells:

...........

If undoing Then Exit Sub

If undoing = 0 And (Target.Columns.Count > 1 Or Target.Rows.Count > 1) Then
MsgBox "illegal operation."
undoing = 1
Application.Undo
undoing = 0
Exit Sub
End If

...........


' undoing is declared globally.


It works fine when i select 3 cells and hit delete, but when i drag and drop
it doubles the undo and cells go to the dragged place.
What to do? ( I am guessing that a "drag and drop" triggers the change event
2 times. )

Moreover, this way ( to undo with the help of the boolean undoing) seems
ugly to me. Any suggestions?

 

[JE McGimpsey]

See

http://j-walk.com/ss/excel/tips/tip23.htm

 

[serdar]

Thanks.
I am not worried about the changes that my routine does.
Let me put it this way: How to block user from drag and drop? Because this
makes my change event works 2 times (I'm not sure about this though.).

 

[JE McGimpsey]

You can put this in your Workbook_Open event, then toggle it on and off
in your Workbook_Activate and Workbook_Deactivate events (so that it
doesn't interfere with your users other workbooks):

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

 

[serdar]

Can i test, whether the last action done is a drag and drop or not? I don't
want to restrict drag and drop for all the worksheets in the workbook.

 

[JE McGimpsey]

I don't know of any way to test this.

Instead of the Workbook_Activate and Workbook_Deactivate events, you
could disable drag and drop for one worksheet only by placing the code
in the Worksheet_Activate or Worksheet_Deactivate events.