My Blog

VBA Excel–Allow Paste Special Only

17

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.

PLANTING THE CODE


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.

THE CODE


  • 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.

  1. David L. Miller
    David L. Miller08-15-2011

    Of course, one of the problems with the macro pasting values is that the Undo List is then trashed. Unbeknownst to the user, every time he/she pastes, the Undo capability would be “undone”. I notice you reference the last action by looking at the first element of the Undo stack (if I’m reading this correctly):

    UndoList = Application.CommandBars(“Standard”).Controls(“&Undo”).List(1)

    Would there not be a way to preserve the undo list, by reading it in, taking the paste special action, then building the undo list back up?

    Dave

  2. siddharthrout
    siddharthrout08-15-2011

    Yes, you are right regarding the Undo list getting trashed every time a macro runs.

    I believe the undo list can be built back but I guess I will cover that as a separate topic in the future. :)

  3. Jeff Weir
    Jeff Weir11-10-2011

    Ohh, that’s a cool approach! THere’s some code in Proffessional Excel Development (2nd ed) that uses a combination of Application.OnKey to hook cut and paste keys, and command bar handing techniques to stop people using the Copy/Paste options available from the mouse right click and the ribbon too I guess.

    But your solution seems much simpler.

    Interesting: I just discovered that the default behavior of Excel if you are pasting into merged cells is that Excel seems to do a paste special/values. Unless you are cutting and pasting a cell with a named range, in which case Excel will ask you if you want to unmerge the cells, and then will paste the source cell along with named range and formats.

    (Aside: I know merged cells and VBA don’t get along, but I’ve used them in an excel-based procurement form I put together, because I wanted to escape having to make all my input cell in a column the same width. So I resized the rows and columns to be a background grid of 10 pixels by 10 pixels, and then merged blocks of cells together to form input cells exactly where I wanted them. This means all my input cells don’t have to line up down the page, which is good given they are all varying lengths. See https://docs.google.com/open?id=0B1hgC5lSuLjVZmFmNzczZTMtYzNlZi00ZGYzLWE4NTgtODEyNDdmMWQzYjhi )

    Given that they are merged, then for this particular application I don’t need your code. Well there you go!

    Thanks for another great post.

  4. Brian Mishler
    Brian Mishler01-30-2012

    Great post! I have been looking for this solution for a while now. I think there should be one slight change in your code… If UndoString = “Auto Fill”…. should say “If UndoList….”

    Thanks much. I will definitely use this.

    • Siddharth Rout
      Siddharth Rout01-30-2012

      Good catch :) Yes it was a typo. Rectifying it :)

  5. bigteejay
    bigteejay04-21-2012

    According to MS, AddItem (used to add items to a list, such as the one used above)…
    “…will fail if it’s applied to an edit box or a built-in combo box control.”
    http://msdn.microsoft.com/en-us/library/ff862141.aspx

    I’m guessing there is no way to “fix” Undo after the above code has been ran?

    • Siddharth Rout
      Siddharth Rout04-21-2012

      >>>used to add items to a list, such as the one used above:
      I am sorry which part of the code that I pasted above are you referring to? :)

      • bigteejay
        bigteejay04-21-2012

        Sorry, Mr Miller mentioned how your code (though helpful, I’m still picking it apart to see if I can use it) would break “Undo” functionality. I saw you mention that you may investigate the idea of rebuilding the list so that it was “right” again. I was pointing out what I found from MS on the matter of adjusting the List property of native controls… hoping that perhaps someone had found another way to restore “Undo” after implementing your wonderful solution.

        • Siddharth Rout
          Siddharth Rout04-21-2012

          Ah. I see what you mean. I was planning on getting into this but realized that Jan Karel Pieterse has already covered this… See if this helps you in any way?

  6. Brandan
    Brandan08-17-2012

    Thanks. This helped immensely for a project I’m working on — Knowing how to undo a paste was wonderful

  7. M.BALASUBRAMANIAM
    M.BALASUBRAMANIAM09-22-2012

    Mr.Siddharth, thank you so much for your above code. I tried using it in one of my excel projects where I have used macros to clear ranges using a macro button. The macro contains a msg Box. With your code in ‘ThisWorkBook’ when I try to run my clear contents macro I get the error message “Method ‘List’ of object ‘_CommandBarComboBox; failed” Can you help me out please ?

  8. M.BALASUBRAMANIAM
    M.BALASUBRAMANIAM09-22-2012

    My apologies for posting my query here, inspite of your request. I didn’t know how to post it in the forum you had mentioned. Instead of wasting my time figuring out that I thought it better to write it to you directly. My code relating to one of the sheets of my workbook is as follows : Sub TERM_I_clearallinputs()

    ‘ TERM_I_clearallinputs Macro
    ‘ This macro clears all un protected inputs in all TERM I subject xls sheets.

    Dim Response As Integer
    Response = MsgBox(prompt:=” YOU REALLY WANT TO CLEAR ALL INPUTS IN THIS SHEET ? (Click ‘Yes’ to Clear ‘No’ to Continue)”, Buttons:=vbYesNo)

    If Response = vbYes Then

    Range(“D10:D64,G9:O64,T10:T64”).Select
    Selection.ClearContents
    Range(“A1”).Select
    Range(“D10”).Select

    End If

    End Sub

    My other sheets in he same workbook also carries simillar code just to erase data

    Any help from your side will be greatly appreciated.

    Thank you in advance

    • Siddharth Rout
      Siddharth Rout09-22-2012

      The reason why I requested is because there will be lot of exchange of views and this place is not apt for it as it will clutter the article :) To post in any of those forums you have to first register as a new user and then ask your query. I will wait for your link.

  9. M.Balasubramaniam
    M.Balasubramaniam09-23-2012

    I have posted my query in vbforums.com i.e.,

    vbforims –> office development –> MsOf07 VB CODE FOR Excel PasteSpecial Values Only

    Started by mbalas‎, Today 01:03 AM

  10. B Brown
    B Brown06-10-2014

    OMG — THANK YOU!!! I’ve been looking for help with this for a while.