Forced ‘paste as values’

aliali318 

In Excel, there is no built-in option to restrict paste operations to "paste special values" or "paste special unformatted text" without using VBA.

However, you can achieve this functionality using VBA code.

Here is an example of how you can accomplish this:

1. Press Alt+F11 to open the Visual Basic for Applications (VBA) editor.
2. In the Project Explorer window, locate and select the worksheet module where you want to enforce the paste restrictions.
3. If the worksheet module does not exist, right-click on the sheet tab, choose "View Code," and paste the code into the new module.
4. In the code window, enter the following VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    
    ' Check if the pasted content contains formulas
    If Application.CutCopyMode = xlCopy Then
        If Not Intersect(Target, Me.UsedRange) Is Nothing Then
            If Target.HasFormula Then
                Target.PasteSpecial xlPasteValues
            End If
        End If
    End If
    
    ' Check if the pasted content is from Word
    If Application.CutCopyMode = xlCopy Then
        If Not Intersect(Target, Me.UsedRange) Is Nothing Then
            If Application.ClipboardFormats(1) = 2 Then
                Target.PasteSpecial xlPasteValues
            End If
        End If
    End If
    
    Application.EnableEvents = True
End Sub

      5. Save the workbook and close the VBA editor.

With this code in place, whenever a user pastes something into the protected worksheet, the code checks if the content has formulas or if it is copied from Word. If either condition is met, the pasted content is converted to values.

Please note that this VBA solution overrides the default paste behavior, so users will not be able to paste anything other than values or unformatted text. It is recommended to inform your users about this restriction to avoid confusion.

Remember to save your Excel file as a macro-enabled workbook (.xlsm) if it contains VBA code.