VBA Excel–Allow Paste Special Only

While recently answering a post on the MSDN forum, I realized that the Robert Gold(Vice President of Business Intelligence in Bostwick Laboratories, Inc) was facing the same problem that I was facing couple of years back when I was working as a Team Leader in one of the BPO’s. The MIS Department was reporting to me and one of the main jobs was to create “Trackers” for various departments. My team used to take hours to design a particular tracker only to realize after few days that the users were spoiling the format of the tracker by copying and pasting. Completely protecting it with a password was not the option. Nor did we find any option in Excel which forced selective pasting. It was then we devised this macro which allowed the user to paste but behind the scenes, converted that paste into paste special – values.

The only drawback of this method is that if the macros are disabled then this won’t work. But thanks to the IT Department, they ensured that the macros was enabled on each and every pc. After that we never faced a problem with users messing up the format of the sheet.

So how does this code work? What is the logic behind it? I would explain this in 3 sections.

  • Logic
  • Planting the Code
  • Code


LOGIC


Whatever action that you perform in Excel Spreadsheet is stored in a list which is called the Undo List. So when you do an Undo for the first time, Excel refers to this list and then Undoes the last action that you performed. So if we can undo a ‘Paste’, then we can save the format of the sheet. All we need to do is:

  • Undo a paste
  • Do a Paste Special – Values

There are few things that we need to take care of in this logic.

  • We not only check for a “Paste” in the Undo list but also for an “Autofill” as Autofill can also spoil formats in a workbook.
  • Don’t clear or set new text in the Clipboard between an UNDO and PASTESPECIAL else you will loose the original data stored in a clipboard.

PLANTINGTHECODE


It depends on your requirement. Do you want to preserve the format in all sheets in the workbook or just one sheet?

If you want to preserve the format in all sheets in the workbook then follow these steps:

  • Open the VBA Editor
  • Double click on “ThisWorkbook” to open the Code Area on the right as shown in the picture below. Paste the code (Code I – Given below in the Code section) in that Code Area.

If you want to preserve the format in only one sheet in the workbook (Say Sheet1) then follow these steps:

  • Open the VBA Editor
  • Double click on “Sheet1 (Sheet1)” to open the Code Area on the right as shown in the picture below. Paste the code (Code II – Given below in the Code section) in that Code Area.

THECODE


  • Code I : Preserve the format in all sheets in the workbook

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim UndoList As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error GoTo Whoa
    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
    Then GoTo LetsContinue
    '~~> Undo the paste that the user did but we are not clearing
    '~~> the clipboard so the copied data is still in memory
    Application.Undo
    If UndoList = "Auto Fill" Then Selection.Copy
    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.Select
    ActiveSheet.PasteSpecial Format:="Text", _
    Link:=False, DisplayAsIcon:=False
    Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    On Error GoTo 0
    '~~> Retain selection of the pasted data
    Union(Target, Selection).Select
LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.description
    Resume LetsContinue
End Sub

  • Code II : Preserve the format of a single sheet in the workbook

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UndoList As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error GoTo Whoa
    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
    Then GoTo LetsContinue
    '~~> Undo the paste that the user did but we are not clearing the
    '~~> clipboard so the copied data is still in memory
    Application.Undo
    If UndoList = "Auto Fill" Then Selection.Copy
    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, _
    DisplayAsIcon:=False
    Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    On Error GoTo 0
    '~~> Retain selection of the pasted data
    Union(Target, Selection).Select
LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.description
    Resume LetsContinue
End Sub

And you are ready. Now when the user pastes in the worksheet, the data will be pasted as “Values” only.

Source: VBA Excel–Allow Paste Special Only

Leave a Reply

Your email address will not be published. Required fields are marked *