Forced ‘paste as values’

Copper Contributor

Hi, I hope someone can help

I have an Excel Table used by multiple users.  The Table has a number of conditional formatting rules that I need to maintain.  Users sometimes paste in text from other sources, which messes up the conditional formatting rules.  If I protect the sheet, the Table doesn’t automatically expand.

Is there a way to only allow paste as ‘paste special values’ (and ‘paste special as unformatted text when pasting text from Word)?  Or a way that forces all pastes to be a paste as values/unformatted text?  Ideally, a non-VBA solution, but happy to use VBA if not possible.

many thanks in advance 


3 Replies


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.

thank you so much @NikolinoDE for the step by step instructions and code; much appreciated.

just one clarification (& perhaps I should have been more precise); I want anything pasted in )from the clipboard) to be entered as ‘values’ - the source could be Excel, Word, web etc.  (the only reason I mentioned Word, was that I noticed when pasting into excel any text copied from Word, the paste special option is ‘unformatted text’, rather than ‘as values’ when copied from excel).

would the code you provided work for anything being pasted in from the clipboard, regarding of source?

many thanks again

Also, does the sheet need to be protected? I was hoping to avoid protecting, as this restricts the Excel Tablw from auto-expanding when new rows are added to the data. Thx