Replace paste with pastespecial (user side only)

[Gringoire]

Hello guys,
To avoid importing undesired wrong external references in my sheet by a User, I found this snipped on the internet. It works correctly replacing a paste by a pastevalues.
The only issue is when I use an internal VBA procedure to programmatically paste some range :
1) it rise an error on Application.Undo line.
2) it prevent my Paste operation (that is in this case required by myself)

So, my question is: is there a way to detect if the paste operation comes from a User (unwanted) action or from a (wanted) code routine?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.CutCopyMode = xlCopy Then
        Application.EnableEvents = False
        Application.Undo
        Target.PasteSpecial Paste:=xlPasteValues
        Application.EnableEvents = True
    End If
End Sub

many thanks

 

[FormR]

Hi, in your "internal procedure" you can disable events before the code makes changes to the worksheet with Application.EnableEvents = False and at the end of that procedure, you will need to re-enable events with Application.EnableEvents = True.

 

[Gringoire]

Hmm.. it seems a good possibility. I'll give it a try even if I already have dozen of sheets and modules with tons of (sometime nested) procedures in my workbook, calling several paste or insert instructions and catch all of them could be a little tricky.

If there is no other way to separate internal paste calls from the User ones, I will use your workaround.

thank you.

 

[FormR]

I already have dozen of sheets and modules with tons of (sometime nested) procedures in my workbook

Sounds like it's a bit of a beast

 

[Gringoire]

Yeah, this issue is related with a new feature that I would like to add to a five months work on vba code .
Unfortunately I realized too late the usefulness of that feature
I will try with this, hoping that it will not introduce new issues:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.CutCopyMode = xlCopy Then
        Application.EnableEvents = False
        On Error GoTo internalp
        Application.Undo
        Target.PasteSpecial Paste:=xlPasteValues
        Application.EnableEvents = True
    End If
internalp:
    Application.EnableEvents = True
    Exit Sub
End Sub