Locking edited cells

I hope someone can help, my VBA is some what limited, its been a long time.
The code I have put together is working ok apart from a few small problems.
Im hoping someone can look at my code and tell me where i'm going wrong.

If I explain what it is ment to do first, then this should make things easier.
Basically, when a user inputs any text in to a cell the cell automatically creates a comment of when, what and who by, and then locks the cell preventing further editing. This all works fine. So whats the problem? Users have started using copy and paste when multiple entries are required. If a single protected cell is copied to another single cell then the code still works fine, but if a single cell is copied and pasted into multiple cells then the value is copied to the cells, but the multiple cells do not lock? If the value in these cells is edited further then they lock.
So basically I need the code to work when multiple cells have been pasted, rather than just one cell being edited.

Hope someone is able to help?

Here is the code:-

Please Login or Register  to view this content.

Please take the time to actually read the Forum Rules that you agreed to when joining. Your post title & lack of Code Tags break the forum Rules. I will edit this once onlY!

Well, for starters, the following code line checks to see if there are more than 1 cell in the area being changed, and if there are then it exits the routine altogether, so that is why it does nothing when there are multiple cells being changed.
So you need to move that line, and then code the rest of the function to deal with multiple values, (that is, Target containing a range of more than just one cell).

However I have tried a couple of variations around this and all I get is questions

- After a cell is edited your code locks the cell and protects the sheet;
-- Presumably when originally setting the sheet up you have UNLOCKED all the cells on the sheet? Otherwise as soon as you protect it no-one can do anything more anyway.
-- Once you have locked a cell, it can't be changed without unlocking the sheet, so how would the 'comment' ever build up a history?
-- Do your users have the 'Unprotect' password, and if so what is the point in protecting the worksheet in the first place?

- Finally, assuming your users have the 'Unprotect' password so that they can edit the sheet, if you allow them to copy & paste (even a single cell to a single cell), the COMMENT is also included in the copy, so it destroys the history of that cell.

I don't believe you can stop them BEFORE they paste the data in, thus destroying your data and history, so perhaps you should be stopping them from being able to paste at all?

Please try the following code and see if it suits your purpose.
There are a couple of things that might trip up the 'comments' section, but from the way it is being used I don't think you will encounter them.

Of course if you have any questions please do ask

Please Login or Register  to view this content.