Prevent worksheet Change if copy or cut used

[YasserKhalil]

Hello everyone
I have the following code in worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Columns("H:H")) Is Nothing Then
        Target.Offset(, -2).Value = Date
        If IsEmpty(Target) Then Target.Offset(, -2).Value = ""
    End If
End Sub

If I entered any value in column H , a date is inserted in column F
I need to prevent the worksheet change if the user used copy or cut ..
Is it possible?

 

[Chihiro]

Do you want to prevent copy/cut all together for a specific range?

If so, refer to link.
http://chandoo.org/forum/threads/cut-copy-paste-disable.25181/

The source is from.
http://www.mrexcel.com/forum/excel-...-copy-paste-only-certain-cells-worksheet.html

It may also give you hint as to how to apply it to your needs.

 

[YasserKhalil]

Thanks a lot for reply
I don't need to disable copy and cut .. I need to use copy & cut but not to update column F in worksheet change

 

[Chihiro]

Okie wasn't sure what the purpose was....

Try something like...

If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
Exit Sub
Else
End If

 

[YasserKhalil]

Thank you very much Mr. Chihiro
That's awesome and wonderful
Best Regards

 

[Debaser]

Or just:

If Application.CutCopyMode <> False Then

 

[YasserKhalil]

Thanks a lot Mr. Debaser
But the worksheet updated with the cut ..!!
The same problem with Chihiro suggestion

It is ok for copy and actually prevent the worksheet update but doesn't work for cut
Any idea?

 

[Debaser]

The cut actually completes before the change event is triggered and therefore, the application is no longer in cutcopymode. I think you would have to use the selectionchange and activate events to monitor if cut mode is engaged and store the result in a variable which you can test in the Change event (and reset afterwards).

 

[Debaser]

Perhaps try this approach:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oUndo
    Set oUndo = Application.CommandBars.FindControl(ID:=128).Control
    If oUndo.ListCount > 0 Then
        Select Case LCase$(Split(oUndo.List(1), " ")(0))
            Case "paste", "drag"
                Exit Sub
        End Select
    End If

End Sub

 

[YasserKhalil]

That's awesome Mr. Debaser
Really wonderful
I tried your approach like that

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oUndo
    Set oUndo = Application.CommandBars.FindControl(ID:=128).Control
   
    If Not Application.Intersect(Target, Columns("H:H")) Is Nothing Then
        If oUndo.ListCount > 0 Then
            Select Case LCase$(Split(oUndo.List(1), " ")(0))
                Case "paste", "drag"
                Exit Sub
            End Select
        End If
       
        Target.Offset(, -2).Value = Date
        If IsEmpty(Target) Then Target.Offset(, -2).Value = ""
    End If
End Sub

But there is a little problem .. when trying to clear the contents of cells in Column H say range("H8:H14") the same range in the F8:F14 is filled with dates and it is supposed to clear the contents of the same range F8:F14

 

[Debaser]

You need something like:

If len(Target.Cells(1).Value2) <> 0 then
 Target.Offset(, -2).Value = Date
       Else
 Target.Offset(, -2).Value = vbnullstring
End If

 

[YasserKhalil]

Thank you very much for this great and awesome solution
Now it is perfect for me ... working like charm
You are GENIUS