Trapping Copy To Range Before Copy/Cut Paste

**Rasm** · 04-04-2011, 10:44 PM

How can I trap using VBA when a user copies i.e. a single cell into a range of say 20 cells. I am looking to trap what range the user copy to (not from) - I use data validation - but it seems the user can copy "invalid" values into my "secure/validated" cells.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**Admin** · 04-04-2011, 10:48 PM

Hi,

Data validation is not 100% safe. You could try Form controls or ActiveX controls or even Userform.

Kris

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**Excel Fox** · 04-05-2011, 03:46 AM

You could identify the range that is being selected after the user has copied a range by using the following code in the respective sheet module (here sheet is where the user is selecting the cells to paste)

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode <> False Then
        MsgBox Target.Address
    End If
    
End Sub

**Rasm** · 04-05-2011, 04:49 AM

Thanks - I will try that - Right now I use the selectionchange in the workbook - so looks like I can use the code there as well - I check it out

**Rasm** · 04-07-2011, 07:48 PM

ahhh - as it turns out - I can trap the paste part like this - so your code Kris - I can use to trap the copy part.

So now I can trap both events - hehehee - I do it all in ThisWorkbook - but that makes no real diff

Anyway - Now that I know how to trap both mouse actions - I can move forward

Code:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    ActiveSheet.Label1.Caption = "Right click=" & Target.Address
End Sub