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

Continue reading “VBA Excel–Allow Paste Special Only”