Catch a Copy/Paste Source Change

In short, I'd like to know if there's a way to catch the source of a copy if the user changes the source of a copy operation mid-copy.

I'd like the users of my worksheet to be able to insert and delete rows and columns as normal, but there are a few special rows and columns with named ranges that I can't let them delete. To work through this issue, I added a routine to the Worksheet_BeforeRightClick event:

Please Login or Register  to view this content.

This works, but it breaks copy/paste if the user right clicks to paste and the sheet protection changes. Changing the protection causes excel to forget the copy/paste source

To get around that issue, I added a routine to the Worksheet_SelectionChange event that keeps track of any cut/copy sources, and another that replaces the cut/copy source after a worksheet protection change

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This is all more or less working but the problem is that if a user copies a range then (without cancelling the operation or pasting) copies a different range (as if they had made a mistake and changed the source), I don't have a way to update SavedRange to the new copy range.

Am I going about this is the wrong way, or is there a way to spot a copy occur when another copy is already in progress?

Originally Posted by natefarm

Here's my suggestion. I played around with it and think it will work and requires no code. Select all your cells and change protection to Unlocked. Select the ranges you want protected and change to Locked. Select Protect Sheet and check most all, if not all the checkboxes. This will allow the user to do nearly everything on the sheet, but if they try to delete a row or column that contains a locked cell, it won't be allowed.

Hi Natefarm! Thank you for your help. I'm sorry I didn't reply right away; I thought I had subscribed to this thread but I guess I hadn't.

Anyway I like your idea. That's actually how I had started out on the project. It does work in general. My added complication is that some of the protected cells are in the header of a table. If any part of the table is locked (including the header), the user can't sort or filter the table (as far as I can tell).

Originally Posted by natefarm

Even if the Sort and Autofilter checkboxes are checked when you protect the sheet?

I believe that is correct. It has been a while since I tried it myself and I'm away from my workstation right now but I remember trawlling through threads like this one at the time:

http://www.mrexcel.com/forum/excel-q...ted-table.html

Tomorrow I'll give it another go. I might try locking the cells just above the table to see if that can work.