CutCopyMode is never xlCut!?

[Joe HM]

Hello -

I have the following code in my Worksheet_Change() function. It was
actually taken from the Excel Help (2003/11.# SP1) ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Target.Row > 4 And (Target.Column = 3 Or Target.Column =
4)) Then
Select Case Application.CutCopyMode
Case Is = xlCopy
MsgBox "In Copy mode"
Case Is = xlCut
MsgBox "In Cut mode"

Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True

Application.CutCopyMode = False

Case Is = False
MsgBox "Not in Cut or Copy mode"
End Select
End if
End Sub

I am trying to only allow a cut/paste into certain cells. This works
fine for the copy/paste (xlCopy) but it is not working for the xlCut.
When I go to the Worksheet and do a Cut and then paste it somewhere
else, it tells me "Not in Cut or Copy mode".

Is there a bug in Excel or am I doing something wrong? As I said, it
works for the copy/paste.

Thanks!
Joe

 

[Guest]

On a "plain" worksheet (without your code) observe what happens:
I copy a value. The "marquee" appears around the cell, showing I have
copied it. I paste, and the marquee remains. I am in copy mode (still).
Now I cut a value. The marquee appears and now I am in Cut mode (but note:
I have not changed the worksheet yet!). I paste, and the marquee goes away -
now I have changed the sheet, but I am no longer in cut mode. So you can't
detect cut mode by the change to the sheet that takes place when you do the
paste.

I added a little workaround: I use a public variable and the
Worksheet_SelectionChange event to log which mode we are in when the cell is
selected (before the paste):

Public WhichMode as Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
WhichMode = Application.CutCopyMode
End Sub

Now just use WhichMode in your sub instead of Application.CutCopyMode (but
note - this now fires the change event twice when you cut and paste! (I
haven't figured out why)

 

[Joe HM]

Hello -

Thanks a lot for your help. The public variable is working and I think
I figured out why the change event is triggered twice. Despite the
Application.EnableEvents = False, the .Undo will trigger another call
to _Change because it is executed after the Application.EnableEvents is
set back to True. So I used another public variable that I set to
False. At the beginning of _Change I check for that and ignore the
event if the variable is set to False. Then I set it back to True so
that the _Change is "turned back on".

The only problem I have not been able to solve, is how I can prevent
the dragging of a cell into another cell. The SelectionChange will
fire but it would be tricky to figure out that it changed due to a cell
being dragged.

Thanks again,
Joe