Writing Code to Allow Pasting Over Protected Cells

[kmk007]

Hello all! This is my first post on this website. I have found you all to be very helpful!! so thanks!!

I am currently building a spreadsheet that our company and several other companies will be using. Columns A-L, columns N-O, and columns Q-S are unprotected columns where the user can enter whatever they want in each new row of data entry (although there are dropdown options for them to choose from, otherwise they just type in what they want). Columns M, P, and T are protected cells which I do not want anyone to tamper with or paste over when they are adding data.


As I'm sure you're already thinking, you cannot take an existing row of data and paste it in the next row with protected cells intermingled here and there. This is my issue, because we work with a lot of repetitive data where only a few columns are changed on every row sometimes. So it would be nice to just go to the left side of the spreadsheet, highlight the row, copy it, paste it for our next entry, and then make the necessary changes to this new row (often only one or 2 columns need changed in the new row to reflect what we want).


I am bad with excel vba... It is my pitfall. I have done a lot of searching for the correct code that makes this process work. I know it has a lot to do with having the code unprotect the sheet, allow the paste, and then reverting protected cells back to their original formula/value before re-protecting the sheet.

Anyone care to share their expertise?


Thanks!!!!!

[Zer0Cool]

Before we dive into the how, lets talk about the why. Questions like this are generally best solved by changing the design of the file, not trying to get a feature to both work and not work at the same time.

Why do those cells need to be protected on the data entry sheet?

Are you aware that copy/pasting whole rows is a bad idea in the fashion you described? By selecting a column or row header and copy/pasting you are literally telling it to copy/paste every column over or every row down even if its empty. This often leads to files with excess content/cells (when Excel thinks the used range stretches beyond your last actually used row/column) and/or excess formatting. This in turn leads to bloat and eventually corruption in the file.

I would advise only copying/pasting a range of cells that actually have content. IE if A1:F1 has content you need to copy, instead of selecting row 1, you select cells A1:F5 to then copy paste. Selecting cells like this becomes easier when you know keyboard shortcuts like CTRL+Shift+Arrow (up/down/left/right) to select cells in a given direction with continuous data in them.

Why do I bring this up...a solution to your issue is to often place cells that require protection to the right of the data entry columns. IE is A1:F1 are for data entry, then G:I may be protected cells. If you only select A:F to copy/paste, the protected cells wont impact this at all.

Another potential option is to split data entry and calculation. Generally people protect cells to prevent accidentally changing formulas. A simple solution is move formulas to another sheet, and make the data entry sheet just that, data entry only. Basically separate calculation and data entry.

If thats not an option, and VBA is a must, you might have a hard time about it as there is no copy/paste event that I am aware of. The closest would be worksheet_change and then you may some how be able to account for a copy/paste. You would have to toggle sheet protection off then back on in said event/code. There are multiple problems with this...

If macros are not enabled, this code never runs. If the code is stopped prior to completion, the sheet remains unlocked. If someone goes and peeks at the code, they can easily get the password and keep it unlocked.

If you are using the sheet protection in place of security, then you are going to be disappointed. Protection <> security. In other words the feature is meant to prevent mistakes, not keep data secure. Its great to prevent cousin Larry in "HR" from destroying a formula, but its like putting a screendoor on a submarine if you are trying to use it to prevent people from getting to data (like what formula you are actually using to calculate that bonus). There are multiple reasons for this, mainly that its very easy to circumvent (I can "crack" a sheet protection password of any length manually with a text editor in under 1 minute).

If you are using it as intended, then I highly recommend changing the layout and how you use Excel (like copying and pasting ranges vs whole rows) vs trying to approach this with VBA.

[kmk007]

Unfortunately, the cells that require protection cannot be moved around or moved to the end of the data. This new spreadsheet has to work with existing/old spreadsheets and data. And yes, it is to protect formulas that we find people commonly change or mess up and it causes headaches down the road because it was not caught the first time.

We cannot really add these formulas to a new sheet. I have already thought of all of these scenarios so far. It would not work well with the way we obtain and record data.

I guess the best way to avoid issues is people will have to copy/paste 3 separate times for each portion of the row that is separated by a protected cell (PITA... but can be done).

I agree, the best way would be to change the order/layout of these cells, but at this point I cannot.

[kmk007]

Another issue I have come across seems to be that I cannot lock the cells with dropdown lists. I believe it is because I have made an intricate working of dependencies that is causing the dropdown to not "lock". In other words, you can still enter in whatever you want in these cells even though I have enabled the error alert in the data validation. However, the columns that really dont have many dependencies set up work just as they should. Has anyone ever ran into this? I wish I could post my spreadsheet but I am sure that causes issues on both ends.....