Results 1 to 5 of 5

Thread: Trapping Copy To Range Before Copy/Cut Paste

  1. #1
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14

    Trapping Copy To Range Before Copy/Cut Paste

    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
    Last edited by DocAElstein; 06-10-2023 at 04:25 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Last edited by DocAElstein; 06-10-2023 at 04:30 PM.

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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

  4. #4
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    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

  5. #5
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    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
    Last edited by Rasm; 04-07-2011 at 07:48 PM. Reason: tupo
    xl2007 - Windows 7
    xl hates the 255 number

Similar Threads

  1. Copy/Paste Excel Range/Chart into Powerpoint VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 03-13-2014, 02:59 PM
  2. Macro Copy Columns and Paste in rows.
    By TommyKris in forum Excel Help
    Replies: 3
    Last Post: 03-06-2013, 02:36 PM
  3. Replies: 2
    Last Post: 02-11-2013, 08:13 PM
  4. VBA -- Copy/Paste across sheets
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 09-21-2012, 02:07 PM
  5. Replies: 2
    Last Post: 04-08-2012, 09:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •