Force user to paste special / values instead of paste?

TheWennerWoman

Board Regular
Joined
Aug 1, 2019
Messages
71
\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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,442
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.)

VBA Code:
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.
 
Solution

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,442
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.

VBA Code:
' 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

New Member
Joined
Mar 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,708
Messages
5,654,854
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top