Force user to paste special / values instead of paste?

[TheWennerWoman]

\hello,

We have a spreadsheet with some considerable data validation and conditional formatting to prevent "dirty data" from being entered (no trailing spaces, no leading spaces, max 35 characters etc) but we've found that if users paste data in then it bypasses the conditional formatting (I think the data validation is still there).

If users want to paste into columns D, E, F and H, is there a way I can force them to paste special values? Even if it's just a message box telling them they can't paste that will be better than nothing.

Many thanks.

 

[smozgur]

I don't like and suggest using the Worksheet_Change event as it will be executed in every change in the worksheet, so I am not sure if this will be ideal solution, but this is a known workaround for this scenario: checking the Undo list in every change in that worksheet might be a solution.

Copy and paste the following code into the worksheet class module (Worksheet_Change event procedure) that you want to avoid paste action.
(If it is Sheet1 then find the Sheet1 class object in the Microsoft Excel Objects in the VBAProject, and double click on the Sheet1 class to open the module window.)

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If InStr(Application.CommandBars("Standard").Controls("&Undo").List(1), "Paste") = 1 Then
        If Err Then Exit Sub
        Application.Undo
        MsgBox "Paste action is not allowed in this worksheet.", vbOKOnly + vbExclamation, "Paste action not allowed"
    End If
End Sub

Basically, checking if the most recent action in the undo list starts with "Paste" words (so it also avoids Paste Special), and undo the last action, and alert the user if it was a paste action.

 

[TheWennerWoman]

Many thanks, works a treat - neat workaround

 

[smozgur]

Many thanks, works a treat - neat workaround

You're welcome.

I just realized that you want to make it work in certain columns, so the following might be better to avoid execution in other columns.

Actually, it should be implemented better to avoid undoing previous "paste" actions that might have recorded in other sheets or ranges in the same sheet. You will notice that the previous paste action that might have happened somewhere else is also going to be canceled and it might cause unwanted results. Perhaps storing this action in a class variable will be a better and complete solution.

Please remove the previous code that you used in the worksheet class, but this time, copy and paste the following code into the ThisWorkbook class to make it work for all sheets (to verify the paste action that might have been done in the other sheets).

Note: Remember to update the Sheet1 object name and columns in the Range("D:F,G:G") according to your worksheet name, and columns need to be restricted.

' Control variable - paste this in the General/Declarations section
Dim actionTaken As Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' If the event procedure is triggered because of the Application.Undo in the previous event
    ' or if this worksheet is not our worksheet
    ' or if the cell is not in the range that we try to avoid paste action
    ' Remember to change Sheet1 and Range("D:F,G:G") if necessary
    If actionTaken Or Not Sh Is Sheet1 Or Application.Intersect(Target, Range("D:F,G:G")) Is Nothing Then
        ' then reset the control variable
        actionTaken = False
        ' and exit here
        Exit Sub
    End If
   
    ' Prevent paste action here
    On Error Resume Next
    ' Is the action just happened a Paste action?
    ' And are we sure this is not a trigger of the Application.Undo?
    If InStr(Application.CommandBars("Standard").Controls("&Undo").List(1), "Paste") = 1 And actionTaken = False Then
        ' then set the control value as True
        actionTaken = True
        ' If there was no item in the Undo list, then it generates error
        ' exit here if it is an error
        If Err Then Exit Sub
        ' Congratulations, we found an illegal paste action, revert it
        Application.Undo
        ' Warn the user
        MsgBox "Paste action is not allowed in this worksheet.", vbOKOnly + vbExclamation, "Paste action not allowed"
    End If
End Sub

I tried to write comments to explain what's happening, but please let me know if you have further questions.

 

[maurig]

Hi Smozgur,
I really like your code ad used many times but now I'm facing a problem. I have to share the excel (with the macro) with people in other countries who use a different language for excel, not English. So "Paste", "Undo" won't work on their computer since I hard code them in the VBA.

Do you have any suggestions on how to adapt your code regardless of the language?