Restrict Paste to Paste Values using VBA

[pecosvil]

Hi everyone. I have a spreadsheet that users will have to populate. Some cells are totally locked, others are available for users to input data, others have data validation (lists).

The first question:
I want users to be able to Paste as Values in certain cells (ie, I don't want them to just Paste as it will modify the Formatting). I took this code from a website and modify it a little bit.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This code will undo PASTE and instead show a message asking for Pasting as Values.
' This allow you to retain FORMATS in all of the cells in all of the sheets, but will
' also allow the user to COPY and PASTE data
' Since this subroutine is located in an Object Module, it should only affect this worksheet.
' Just in case, Disable and EnableEvents lines have been added in Module_ConsistencyChecks, Module_Output
' and Module_Reset to avoid EventLoops
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim UndoString As String
    Dim Msg, Style, Title, Response, MyString ' For the MsgBox
    On Error Resume Next ' Next line is prone to error
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
    
    If Left(UndoString, 5) = "Paste" And UndoString <> "Paste Special" Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.Undo
            Msg = _
            "You are pasting information. Doing that will modify the Data Validation and corrupt the file." & _
            "The operation was undone. If you want to paste data, please select paste as values. Thanks!" ' Define message.
            Style = vbOKOnly    ' Define buttons.
            Title = "Invalid Action"    ' Define title.
            Response = MsgBox(Msg, Style, Title)
            Worksheets("Loan_Information").Protect
    End If
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

The code as it is now restricts the user from simply Pasting, but allows them to use "Paste Special" and then lets them select "All" -ie xlPasteAll-(or Formatting, or other option). I want to restrict that. I just want them to be able to Paste as Values (xlPasteValues).

Also, and I think this is part of the problem, I don't entirely understand the line"

Application.CommandBars("Standard").Controls("&Undo").List(1)

The second question:
Somewhat related. For those cells that have data validation (lists), I want users to be able to copy and paste as values. Sometimes they have 200 or 300 rows to complete, and many of them have the same element from the list (if the list are countries, many of the entries have USA). I want to avoid them the work of typing "USA" or clicking the drop-down list and selecting "USA" fifty times. But I don't want them to copy and paste as values text that is not valid (ie, not in the list). The alternatives that I found on the internet always lead to disable cutcopypaste. That's not helpful for me as I want them to be able to copy and pastevalues. Any insights on this one?

As always, many many thanks!

 

[pecosvil]

Anyone?

 

[Panos]

YES, I GOT IT. FOR MANY YEARS I HAD THIS PROBLEM AND NOW YOU INSPIRED ME.
I WAS DOING AN ALTERNATIVE WAY. I WAS KEEPING ONE ROW WITH ORIGINAL FORMAT AND I "REPAIR" FORMAT AFTER AN ACTION.
WHAT IS MY WAY OF THINKING: AT ANY "PASTE EVENT" (ALL, EITHER PASTE SPECIAL) I UNDO, AND THEN I "PASTE VALUES". ALSO, I DISABLE THE MESSAGE YOU HAD, NOT NECESSARY.

NOW THE SOLLUTION IS AS FOLLOWS:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This code will undo PASTE and instead show a message asking for Pasting as Values.
' This allow you to retain FORMATS in all of the cells in all of the sheets, but will
' also allow the user to COPY and PASTE data
' Since this subroutine is located in an Object Module, it should only affect this worksheet.
' Just in case, Disable and EnableEvents lines have been added in Module_ConsistencyChecks, Module_Output
' and Module_Reset to avoid EventLoops
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim UndoString As String
Dim Msg, Style, Title, Response, MyString ' For the MsgBox
On Error Resume Next ' Next line is prone to error
UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
' MsgBox UndoString & Chr(13) & "(FOR YOUR INFO ONLY)"
If Left(UndoString, 5) = "Paste" Then 'And UndoString <> "Paste Special" Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Undo
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
' Msg = _
"You are pasting information. Doing that will modify the Data Validation and corrupt the file." & _
"The operation was undone. If you want to paste data, please select paste as values. Thanks!" ' Define message.
' Style = vbOKOnly ' Define buttons.
' Title = "Invalid Action" ' Define title.
' Response = MsgBox(Msg, Style, Title)
Worksheets("Loan_Information").Protect
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
On Error GoTo 0
End Sub

 

[Panos]

ABOUT UR 2ND PROBLEM, I HAVE NOT TOTAL SOLLUTION, BUT IT WILL HELP YOU IF YOU USE CONDITIONAL FORMATTING, AT LEAST TO HAVE ERROR CELLS IN RED COLOR.
AN OTHER SOLLUTION IS TO RUN FOR ALL COLUMN A: FOR i =1 to 200, next i .... etc, but not suggested.

 

[rlondon2015]

Hi Panos,

This is great !! However your code unable the user to go back with the undo option. DO you know why ? Moreover, I can't find a way to copy paste from another source (website for instance) the text can not be copy into the sheet.
Would you have any insight ?

Regards,

Rudy