Catching Paste Operations

Content

• Introduction
• Explanation Of Parts Of The Code
• Putting It All Together
• Sample File

Introduction

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 reliable is to catch all possible paste operations. But this isn't very easy, since there are a zilion ways to paste:

• Control+v
• Control+Insert
• Shift + Insert
• Enter
• And of course various menu items and toolbar buttons, which may be located anywhere.

This article shows one way to intercept all these paste operations.

Explanation Of Parts Of The Code

Catching Keyboard Shortcuts

The only way to catch paste keyboard shortcuts is by assigning them to your own paste code. E.g. like this:

(the MyPasteValues routine is shown further below)

Catching Clicks On Toolbar And Menu Controls

To catch clicks on Commandbar controls, I used a class module in which a variable is declared of type commandbarcontrol, using the WithEvents keyword. Then an instance of this class module is created for each control I want to intercept.

The class (named clsCommandBarCatcher), contains this code:

And the code to create an instance of this class could look like this:

To keep the instances of the classes "alive" I create a (module) collection variable and add each instance to the collection.

The paste is handled by this routine:

Note that the code above also mimicks the MoveAfterReturn behaviour of Excel.

Catching clicks on the Excel Ribbon (2007 and up)

To catch clicks on the various Excel 2007 (and up) paste controls, you have to include CustomUI with your Excel 2007-2021, 365 format xlsm file. The relevant Ribbon XML code is listed here:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <commands>
        <command idMso="Paste" onAction="MyPasteValues2007"/>
        <command idMso="PasteSpecial" onAction="MyPasteValues2007"/>
        <command idMso="PasteFormulas" onAction="MyPasteValues2007"/>
        <command idMso="PasteFormatting" onAction="MyPasteValues2007"/>
        <command idMso="PasteValues" onAction="MyPasteValues2007"/>
        <command idMso="PasteNoBorders" onAction="MyPasteValues2007"/>
        <command idMso="PasteTranspose" onAction="MyPasteValues2007"/>
        <command idMso="PasteLink" onAction="MyPasteValues2007"/>
        <command idMso="PasteSpecial" onAction="MyPasteValues2007"/>
        <command idMso="PasteAsHyperlink" onAction="MyPasteValues2007"/>
        <command idMso="PastePictureLink" onAction="MyPasteValues2007"/>
        <command idMso="PasteAsPicture" onAction="MyPasteValues2007"/>
    </commands>
</customUI>

This page by Ron de Bruin clearly explains how to add ribbon customisations to your files

Cell Drag And Drop Mode

Dragging the fill handle is another way to wreck your validation. Hence this is turned off too. Unfortunately changing this setting empties the clipboard, so this setting cannot be toggled at will when switching workbooks. If you do turn it on and off when switching to and from your workbook, copying and pasting from another workbook to the one with the code becomes impossible.

Validation

The code I showed above redirects all paste operations to one routine, which does a paste special, values. But pasting does not trigger any validation checks. Also, there is no way of knowing what cells may be affected by the paste before the actual paste has been done. Luckily, after the paste, the Selection object is equal to the cells affected by the paste. Using the "Validation" object of the Range object it is possible to check whether the cells adhere to their validation settings. I created this function, to which I pass the range that has been changed. The function returns False as soon as any cell violates its validation rule:

Switching On And Off

Of course I want this paste restriction to work on just the workbook with the code. This means some code in the ThisWorkbook module is needed, to be precise in the Workbook_DeActivate and Workbook_Activate events. This is all code I have in ThisWorkbook:

Putting It All Together

The entire module that handles the initialisation and processes the paste operation looks like this:

Sample File

I prepared a sample file so you can see how to put this all together. The download contains two workbooks; one for Excel 2003 and earlier, the other for Excel 2007 and up.