@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:
- Press Alt+F11 to open the Visual Basic for Applications (VBA) editor.
- In the Project Explorer window, locate and select the worksheet module where you want to enforce the paste restrictions.
- If the worksheet module does not exist, right-click on the sheet tab, choose "View Code," and paste the code into the new module.
- 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.
