Catching Paste Operations

You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.

What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, since there are a zilion ways to paste.

I have put together a sample workbook and an explanatory article on how one could go about protecting a workbook by intercepting paste operations.

Let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Posted in Uncategorized

36 thoughts on “Catching Paste Operations

  1. Since the author’s approach requires VBA, better to use Change and Calculate event handlers to check for invalid entries rather than trying to trap all paste operations. Besides, it’s not too difficult to enforce activation of event handlers. Most cell formulas could check a named cell containing a formula calling a udf that returns the value of Application.EnableEvents, and if FALSE, these other formulas could all return error values. OTOH, it’d be much more complicated for a udf to check that all the usual ways of pasting from the keyboard are disabled, and a practical impossibility to check for the user using the right-click pop-up menu to paste.

  2. Preserving formatting can also be handled by Change and Calculate event handlers.

    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim v As Variant, i As Long, j As Long

      On Error GoTo CleanUp
      Application.EnableEvents = False
      Application.ScreenUpdating = False
     
      v = Target.Value
      Application.Undo
     
      If IsArray(v) Then
        With Target
          For i = 0 To .Rows.Count – 1
            For j = 0 To .Columns.Count – 1
              .Offset(i, j).Cells(1, 1).Value = v(i + 1, j + 1)
            Next j
          Next i
        End With
      Else
        Target.Value = v
      End If

    CleanUp:
      Application.CalculateFull
      Application.EnableEvents = True
      Application.ScreenUpdating = True
    End Sub

  3. My favorite client doesn’t care if the user messes up their workbook by pasting over DV, or overwriting formulas, or whatever. He says they’re grownups, and should follow instructions. He also doesn’t like protected sheets, so the chances of hosing the workbook are not infinitessimal. The way the system now works, the code is all in an add-in and the sheets created from templates, so if the user does mess up something, it usually only takes deleting one sheet and (properly) repopulating a new one to get back on track.

  4. John: Nice approach, and yes, I had read it before. My requirements were a bit different, e.g not all target cells have validation, nor the same validation.

    fzz: I considered this approach. But the project I created this for demanded a solution without use of the sheet events. BTW: I see your solution also effectively prevents me from entering a formula in a cell and does not prevent changing formatting by a paste-special operation. Also: Why are you writing the values one-by-one? This would do it too:

    Dim mbDisableEvent As Boolean

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim v As Variant
        If mbDisableEvent Then Exit Sub
        mbDisableEvent = True
        On Error GoTo CleanUp
        Application.ScreenUpdating = False
        v = Target.Value
        Application.Undo

        Target.Value = v

    CleanUp:
        mbDisableEvent = False
        Application.CalculateFull
        Application.ScreenUpdating = True
    End Sub

  5. Doesn’t John Walkenbach’s approach also require sheet events?

    And fewer problems arise from adding classes, remapping keys, and trapping various commandbar controls than from just using sheet events?

    I hadn’t thought about paste special, but on my system running Excel 2003 SP1, the Change event handler is triggered by paste special format operations, so my event handler undoes formatting changes via paste special.

    You have a point about the For loop. Also about overwriting formulas with their values. However, there are situations in which only constant data is expected.

  6. JKP,
    I downloaded the workbook and tried to copy paste using right non default drag drop…it lets me do it …
    Sam

  7. Good work JKP, There also a good bit on this in PED.
    I think it’s a hard thing to get right, Like Jon say, users should know better, and like J-walk says what about Macros off?
    As for formating I tend to re-apply a format is it’s important, via a recored macro, which is much like using a template i guess.
    good stuff

  8. I have a solution that I use where multiple users input to a common spreadsheet. I am going to preface my comments with the acknowledgment that an expert Excel user can get around anything. This solution is in place to protect the data from non experts. I have all sheets very hidden except for the first sheet which states that if you want to use the spreadsheet you must have macros enabled. When launched with macros enabled it unhides the rest of them. Only Column A cells are unprotected. This allows users to navigate up and down the sheet. If they happen to accidentally enter any data in a cell in Column A I have the changed event clear it. The whole point is that they select a Row by mouse click in it’s Column A cell, which then brings up a user form for them to enter data into. All error checking is done within the code for the user form, and as they exit the form their changes are applied to the cells in the Row. When they exit the spreadsheet all the sheets except the first one are again made to be very hidden. This has worked very well for us for about 2 years now.

  9. I’m with Dan. If I want to make a workbook more secure, I don’t mess with all this worksheet stuff. I put together a userform, build validation into it, and usually don’t even let the user know it’s storing data on a worksheet somewhere.

  10. Dan: I do the hiding/unhiding stuff too in the real world app. Might be good to add that to the demo too. The app I wrote this for doesn’t have simple enough layout to enable me to use your userform approach, but it certainly would be a good way to do it.
    Sam: good catch.
    Jon: Point taken, makes sense, but not always feasible.

  11. I think it’s silly we have to do all of this stuff for validation. It seems ridiculous that there’s nothing MS has done to paste cell contents without destroying validation. You can paste special-validation so why can you not paste special-following validation rules. DV could be so great if it actually were fool proof.

    This article caught my attention because this is something I deal with constantly. The best thing that has worked for me so far is just putting a small ‘error check’ button at the top of the workbook, looping through and stopping at each data error so the user can find the bad values and fix them. I use the DV too, but don’t rely on it totally so I end up using both approaches. Also every time the ‘error check’ is done I use code to re-create the data validation on the sheet. That way even if they pasted in blank cells or columns the DV gets reset.

  12. Thanks for this code, it seems to be the best option so far for preserving formatting & valiation but it does not enforce the validation rules nor does it revert back to the previous value upon failure.

    This code also moves the active cell back to the cell in which data was entered instead of where the user expects the cursor to be in the next cell.

    Instead of using “v = Target.Value” in the code above, pasting by formula will preserve both the formulas entered as well as any cell values if there were no formulas.

    I’ve also removed the Application.CalculateFull simply because my Workbook is large and sluggish.

    This is what I’m using:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v As Variant
    Dim activeCell As Variant
    Dim oldValue As Variant

    If mbDisableEvent Then Exit Sub
    mbDisableEvent = True
    On Error GoTo CleanUp
    Application.ScreenUpdating = False
    Dim r As Range

    ‘ Get the value/formula entered
    v = Target.Formula
    ‘ Determine where the cursor is currently so we can revert back to here if data entry successful
    activeCell = Application.activeCell.address
    ‘ Remove the user’s pasted object and replace with the formula/value only
    Application.Undo
    oldValue = Target.Formula
    Target.Formula = v

    ‘ Check if the validation passed or failed
    If Target.Validation.Value = False Then
    ‘ Give error message
    MsgBox “Warning!!!” & vbNewLine & vbNewLine & _
    “The paste operation has caused illegal entries to appear” & vbNewLine & _
    “in one or more cells containing validation rules.” & vbNewLine & vbNewLine & _
    “Please check all cells you have just pasted ” & vbNewLine & _
    “into and correct any errors!”, vbOKOnly + vbExclamation
    ‘ Revert to previous value
    Target.Formula = oldValue
    Else
    ‘ If successful put the cursor back where it was before validation
    Range(activeCell).Select
    End If

    CleanUp:
    mbDisableEvent = False
    Application.ScreenUpdating = True
    End Sub

  13. (sorry, forgot my tags in the first post)
    Thanks for this code, it seems to be the best option so far for preserving formatting & valiation but it does not enforce the validation rules nor does it revert back to the previous value upon failure.

    This code also moves the active cell back to the cell in which data was entered instead of where the user expects the cursor to be in the next cell.

    Instead of using “v = Target.Value” in the code above, pasting by formula will preserve both the formulas entered as well as any cell values if there were no formulas.

    I’ve also removed the Application.CalculateFull simply because my Workbook is large and sluggish.

    This is what I’m using:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim v As Variant
        Dim activeCell As Variant
        Dim oldValue As Variant
       
        If mbDisableEvent Then Exit Sub
        mbDisableEvent = True
        On Error GoTo CleanUp
        Application.ScreenUpdating = False
        Dim r As Range
       
        ‘ Get the value/formula entered
       v = Target.Formula
        ‘ Determine where the cursor is currently so we can revert back to here if data entry successful
       activeCell = Application.activeCell.address
        ‘ Remove the user’s pasted object and replace with the formula/value only
       Application.Undo
        oldValue = Target.Formula
        Target.Formula = v
       
        ‘ Check if the validation passed or failed
       If Target.Validation.Value = False Then
            ‘ Give error message
           MsgBox “Warning!!!” & vbNewLine & vbNewLine & _
                “The paste operation has caused illegal entries to appear” & vbNewLine & _
                “in one or more cells containing validation rules.” & vbNewLine & vbNewLine & _
                “Please check all cells you have just pasted “ & vbNewLine & _
                “into and correct any errors!”, vbOKOnly + vbExclamation
            ‘ Revert to previous value
           Target.Formula = oldValue
        Else
            ‘ If successful put the cursor back where it was before validation
           Range(activeCell).Select
        End If

    CleanUp:
        mbDisableEvent = False
        Application.ScreenUpdating = True
    End Sub

  14. John,

    Tried your code, thanks! The message box appears, but the application.undo does not undo the paste operation. Any ideas?

    Thanks,
    Brett

  15. I have been working on this problem lately and I have been searching the Web and forums for information on this subject, and this discussion here have been the best I have found. So I tought I would submit my solution here.

    It is very possible that the users of my sheet will like to copy data from their own sheets onto my sheet, and copy data within my sheet. I also use datavalidation to make sure I can use the data when I get it back from the users.

    My solution is to run a macro when the sheet is opened. This copies the formatting and the datavalidation of my input range to a hidden sheet. When the user changes any cell in the input range, a worksheet change event simply copies the formating and validation back from the hidden sheet.

    That way the use is free to use all of excels copy, cut, paste and drag/drop features without destroying the formating and validation.

    The problem with this is that it is possible to enter values that is outside the validation conditions, because the validation is applied to the cell after data entry. The way I solve this is to recheck the validation with a user defined function. Conditional formatting (bright red colour) then uses this function to inform the user that the data has to be corrected.

    Using VBA to make changes like this in the sheet causes the undo buffer to be flushed. To still be able to do a undo, I also had to create a custom one level undo function.

    I think this solution will be the best for my users. They will be able to use copy/paste like they are used to, and probably want to. Some times some of the data they paste will not be within the validation ranges, but most of it will probably be. The conditional formatting will make them aware of this, and they can manually correct these cell values. I think stopping their paste action with a warning or making it not work will make them frustrated.

    To make backup of validation and formatting (called from workbook_open):

    Public Sub BackupFormat()
        Worksheets(1).Range(“rngInput”).Copy
        Worksheets(3).Range(Worksheets(1).Range(“rngInput”).Address).PasteSpecial xlPasteValidation
        Worksheets(3).Range(Worksheets(1).Range(“rngInput”).Address).PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
        Worksheets(1).Range(“A1”).Select
    End Sub

    This is the code that take care of the restoring and undo
    In sheet1 code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error GoTo xit
        Application.ScreenUpdating = False
        Application.EnableEvents = False
       
        If Not Intersect(Target, Range(“rngInput”)) Is Nothing Then
            MakeUndo
            RestoreFormat
        End If
    xit:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub

    ‘ Undo Button
    Private Sub CommandButton1_Click()
        On Error GoTo CustomUndo
        Application.Undo  ‘ uses ordinary undo if available
       Exit Sub
    CustomUndo:
        DoUndo
    End Sub

    In a module:

    Public Sub RestoreFormat()
        Dim rngSel As Range
        Set rngSel = Selection ‘remember what cell to return to
       Worksheets(3).Range(Worksheets(1).Range(“rngInput”).Address).Copy
        Worksheets(1).Range(“rngInput”).PasteSpecial xlPasteValidation
        Worksheets(1).Range(“rngInput”).PasteSpecial xlPasteFormats
        Application.CopyCutMode = False
        rngSel.Select
    End Sub

    Public Sub MakeUndo()
        Dim ArrayNew As Variant
        Dim ArrayOld As Variant
        Dim rngSel As Range
        Set rngSel = Selection  ‘Remember what cell to return to
       ArrayNew = Range(“rngInput”).Value
        Application.Undo
        ArrayOld = Range(“rngInput”).Value
        Range(“rngInput”).Value = ArrayNew
        Worksheets(3).Range(Worksheets(1).Range(“rngInput”).Address).Value = ArrayOld
        rngSel.Select
    End Sub

    Public Sub DoUndo()
        Dim rngSel As Range
        Set rngSel = Selection
        Worksheets(3).Range(Worksheets(1).Range(“rngInput”).Address).Copy
        Worksheets(1).Range(“rngInput”).PasteSpecial xlPasteValues
        rngSel.Select
    End Sub

    “rngInput” is my defined name for the my user input range.

    I would wery much appreciate any comments from you guys on this, as I have not fully completed my project yet, and maybe I have missed some important parts.

    The conditional formatting is not tested yet, but here is a link to where I think I have figured it out:

    http://www.mrexcel.com/forum/showthread.php?t=296934

  16. Hey Bjørnar,

    Looks great!!
    I’d really like to test this solution. To see if it suits my needs.
    Can you give us a link?

    Thanks,

    Breezback

  17. I have now done some testing on the conditional formatting too. I had to remove the Application.Voilatile statement, as this was slowing down the application siginificantly. The result of this is that the conditional formatting will not update before you enter a cell value. For most uses this does not seem to be a problem. My application uses a language selection option, that changes the validation list. This could make some of the validations conditions false, and will not highlight these errors before next input. I don’t think this will be a problem because i will put in a restriction that does not allow language change after the user has started to do inputs.

    The input sheet is protected as it will be for my users, but there is no password, so feel free to remove the protection. I have also removed a lot of data from my real application, that does not consern this test.

    My main goal with this solution is to not put to much restriction on the users. I think they could easily get frustrated if Excel does not allow the to do what they normaly do. The only drawback is that they only get one level of undo, but in most cases i think this will be enough.

    I would appreciate some input on how you think this works, and if you find any bugs, that should be fixed.

    Here is a link to the test file:
    http://www.savefile.com/files/1315951

  18. Nice job Bjørnar.
    The only drawback I see is that your users could still insert cells or cut and paste cells, which would change the structure of your input area.
    My project also requiered that named ranges be left alone, so cut and paste operations had to be prevented as well (in fact anything that may affect named ranges: a paste from an external file may add range names too).

  19. Thanks for your input. We all have different needs. The application in my example will be used for collecting information that the user probably already have in another Excel workbook, exported from a database. The users will probably like to copy and paste to put the information in the right places. For the columns with drop down list, they will probably use the drop down for one cell, and use drag and drop or copy and paste to fill the rest of the column (or part of the column). The users source workbook wil probably not contain any named ranges, so I think I can live with this.
    The next thing I will like to improve is to get ridd off the Undo button I made and try to make this work with the user using excels standard undo button. If I do this I think a average excel user will not even notice my that the VBA code is doing something with his worksheet.

  20. Hi Everyone,
    Firstly thanks for such a useful and constructive page, lots of great ideas here.
    Bjørnar: Great job, I really like what you have done and intend to borrow some of the ideas as I have a spreadsheet with surprisingly similar needs (creating worksheets which will be imported into access tables, hence having strict data validation!)
    A couple of things spring to mind, firstly in the list column the ability to fill with the ‘+’ in the bottom right corner of each cell is disabled because the drop down is opened by any click on the cell which may be a minor annoyance (interestingly is works fine on the backup sheet so possibly this is deliberate!).
    Secondly, when backing up and restoring the validation and formatting you could consider saving only one row and restoring n times where n is the number of rows (i think vba is fairly capable of identifying the last row). This would mean you do not have to limit the number of rows in the sheet and should bypass the issues of insert/cut-paste since the structure becomes irrelevant. It might be sensible to set n to a minimun of say 50 so the sheet looks like it all has validation on first use.
    Hope this makes sense. Any luck with the amendment of the built in edit button?
    I think in my project I may create the ability to turn on/off the auto validation/conditional formatting and add a button to apply the RestoreFormat sub manually.

    Many thanks

    Al

  21. Well, you can schedule your undo macro to be run when the user clicks any of the default undo buttons:

    Application.OnUndo “Description of Undo” , “YourUndoSub”

  22. but the vba restore code clears the undo buffer so the undo buttons are disabled? or have i missed something….

  23. Thanks for all your feedback.

    I have not had any time too figure out the undo button yet. At first I also tought that the Application.OnUndo would be a easy solution, but I think Alistair is right about that the button will be disabled, when I actually need it.
    The tip about only copying one row is a good tip, and I think I will try to do this. That way I will also be sure that all rows have the same validation and formatting, in case I manage to destroy some of this myself when I make changes to the sheet (with restore function disabled). I then only have to edit and test the first row.
    As Alistair mentioned, I have also noticed that the fill handle (the little black + in bottom right corner) in a cell with drop down list is sometimes difficult to reach because of the drop down arrow beeing on top of it.

    I’m also going to use this solution in another Workbook where cells in other sheets will have references to the input range. I see that this could bring up some problems, as jkpieterse pointed out, if the user insert cells or use cut operations, so I might have to do something about this. For many of these references I already use a combination of INDEX and MATCH referencing only to index cells in row 1 and column A, Which is hidden, protected and outside the input range. I think maybe if I use this solution on all references to the input range, the refferences will still be valid even if the user uses cut operations inside the input range.

  24. I have used the feedback from jkpieterse and Alistair to modify my project. I have now created a class module to take care of the preserving of datavalidation and formatting.
    You can still do as much cut, copy, paste and drag/drop as you like.
    The one level undo works for multiple input ranges.
    I have also added a couple of UDF’s that I use for all references to the input area, so no references gets destroyed when you cut or drag/drop on cells in the input area.
    Inserting cells is prevented by worksheet protection.
    For those of you who wants to take a look at this I have made a demo, available from the link below.
    This demonsrates the use of this class module on 2 separate input ranges. There is also a output range showing the use of the UDF’s that references the input areas. Try cut/paste and you can see that no references is destroyed.
    The use of a template row for all validation and formatting, that Alistair suggested, also makes changes easier, I dont have to copy the changes to all rows, just change the template.
    If the user that puts in the data don’t activate the macro’s, he can still use the workbook, and can probably destroy some formatting and validation. But the template cells is hidden and all invalid data is highlighted and can be corrected, when you reopen the workbook with macros activated.

    http://www.savefile.com/files/1398313

  25. Some thoughts (no code) borrowed from an approach I took to this problem. First, before the workbook is saved, it hides all sheets except a warning sheet which explains that macros must be enabled to view the meaningful sheets. Second, all sheets are PW protected and all the fields are locked except data entry cells. Third, Most editing is done in VBA, so there is little data validation. Finally, whenever there is a change in a data entry cell, the entry is edited, reset to a non-harmful state if in error, and formats, conditional formats, and validation if any is reapplied from a mask on a hidden sheet, customized to that particular cell or range. This has multiple advantages: the user can’t screw up formats or edits (we all do it, usually by inadvertence), 1000 cells never go to div0 or NA or NAME? and force you to debug, it allows paste operations into data entry cells, and it’s relatively secure.

  26. This workbook..
    ‘ Activate/deactivate – we only want to capture ctrl+v on this specific workbook
    Private Sub Workbook_Activate()
    Application.OnKey “^v”, “RunMyPaste”
    End Sub

    Private Sub Workbook_Deactivate()
    Application.OnKey “^v”
    End Sub

    Public Sub RunMyPaste()

    ‘ worksheet is unprotected for VBA, if cell is locked – do not paste (VBA would allow it)
    If ActiveCell.Locked = True Then
    Exit Sub
    End If

    Dim DataObj As New MSForms.DataObject
    Dim S As String
    DataObj.GetFromClipboard

    ‘ Set up for error checking
    On Error Resume Next
    S = DataObj.GetText

    ‘ Clean non-printable characters
    S = Application.WorksheetFunction.Clean(S)

    ‘ Check if error and check if zero length (blank)
    If (Err.Number 0) Or (Len(S) = 0) Then
    On Error GoTo 0
    Exit Sub
    End If

    ‘Paste
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ‘backup cell value
    Dim sValue As String

    sValue = ActiveCell.Formula
    ActiveCell.Formula = S

    If ActiveCell.Validation.Value = False Then
    ‘Give error message
    MsgBox ActiveCell.Validation.ErrorMessage, vbCritical, ActiveCell.Validation.ErrorTitle
    ActiveCell.Value = sValue
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    ‘ paste value again so worksheet_change is processed (colors etc)
    ActiveCell.Formula = S
    End Sub

  27. Dear Bjørnar Tofteland, would you be kind enough to repost an updated link to the corrected code? Thank you. The olderversion looks good. But would like the undo option to work

  28. Hi, I do not want to have a macro in my spreadsheet because some users doesn’t enable macro due to the risk of internet security/virus issues. I am trying to create a timesheet in excel spreadsheets. Each user will have to use the worksheets that named after their name. There will be 3 columns on left hand side,one for charge number, other for part number, and the third for entering Type of document. Since there are several charges numbers, part numbers and document types on which users would work each days. Therefore, I want to use data validation to create the list of document types, charge numbers and part numbers so that users can select the right combination by using drop down list and then enter their daily timesheet hours against the date and day. Date and Days will start from column 4th but split into two rows. Each user worksheets within a spreadsheet is fully protected with a confidential password. I do not want the users to edit the first 3 columns and then date and days rows. However remaining cells are editable because they need to enter the hours in those cells.

    I have shareed the workbook in the network so that every users can access the same spreadsheet and click on their respective worksheets and then select the charge number from column 1, then part number and document type from column 2 and 3 and then enter hours against the date cell. The data validation works fine when the workbook is not shared over the network, but the moment I share it, users won’t able to see the drop down list.
    How do I overcome from this disappearing of drop down list when workbook is shared in network.

    I appreciate your help.

    Thanks,
    Srini

  29. Does anyone have the file that Bjørnar Tofteland’s file from February 23, 2008? If so would you be kind enough to post a link to it? Bjørnar Tofteland’s link does not work any more. Many thanks for all your help.

Leave a Reply

Your email address will not be published. Required fields are marked *