My undo routine fails with drag and drop.

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?

See

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


In article <[email protected]>,
"serdar" <[email protected]> wrote:

> 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?

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" <[email protected]>, haber iletisinde şunları
yazdı:[email protected]...
> See
>
> http://j-walk.com/ss/excel/tips/tip23.htm
>
>
> In article <[email protected]>,
> "serdar" <[email protected]> wrote:
>
> > 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?

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


In article <[email protected]>,
"serdar" <[email protected]> wrote:

> 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.).

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" <[email protected]>, haber iletisinde şunları
yazdı:[email protected]...
> 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
>
>
> In article <[email protected]>,
> "serdar" <[email protected]> wrote:
>
> > 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.).

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.



In article <[email protected]>,
"serdar" <[email protected]> wrote:

> 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.