Mysteries of cell locking/protection

Cell locking/protection has always been a bit of a puzzling mystery to me.
I understand the general concept but putting it into practice has eluded me to this day.

If I understand correctly, once a cell has been locked/protected (I don't know the difference between the two)
a user can't change the contents of the cell.

I went through the steps to lock/protect a range (column) and near the end it asked for a password to 'unprotect' the range. I thought this rather odd, as I would expect a password request to lock/protect the range, not unprotect it!

Anyway, I complied, I gave it a password and it looked like the range was now protected.
I tested it, and got a pop-up when I tried to edit a cell in the range. This was to be expected.

As a bonus, all of my unprotected cells that had previously had the green marker in the upper left hand corner, now were rid of the marker.

Fast forward to just now...
I ran a routine to first copy cells in rangeA onto another worksheet.
Inside that range was my locked/protected range of cells in one column.

The second step of the routine (subject to change of course), was to clear the contents of the rangeA.
I was hoping that Excel would clear the cells of rangeA, but not touch the locked/protected range.
The cells were after all a locked/protected range, were they not?

Well guess what? The locked/protected range was cleared along with the other cells.

So finally my questions.

What must I do to genuinely lock/protect a range and also be able to clear a range that includes this locked area?

Will I be forced to alter the clearing to leave out the locked/protected part? That seems a little silly if the cells are supposed to be genuinely protected.

TIA!

Originally Posted by 6StringJazzer

Cell locking is not hard to understand, if you get a really good explanation.


If the sheet is unprotected, then whether a cell is locked or unlocked doesn't matter.

Thanks for helping out 6String!
I'm still not 100% clear on this but your reply was helpful.
Why would you lock a cell if a sheet is unprotected then?

The green marker is an indicator that Excel has detected a condition that it considers undesirable. I personally think Excel is overly aggressive with this but if you see them you should take the trouble to understand why you're getting them. I am not sure why the marker was cleared in this case.

I carefully went through the protection procedure again just now.
Before protecting my formula cells had the green marker, after protection the green markers were all gone. So far so good.

I think you must have locked the cells but did not protect the sheet. Hard for me to say if I'm not sitting next to you.

This time when I ran the same procedure, Excel stopped with a pop-up and said it couldn't clear the protected cells. So that's a good sign.

To genuinely protect a range, you lock the cells, then protect the sheet. If you run code to clear locked cells, you must unprotect the sheet first, clear, then re-protect the sheet. Attempting to clear locked cells on a protected sheet is not simply ignored, it will cause an error (a runtime error if you are doing it in VBA code).

OK, so now down to the crunch.

How should I go about programming this?
Currently my procedure copies a range from one sheet to another.
Then returns to the copied range. I want to clear the range now.
But since the sheet is protected it won't clear the range.

So how would you recommend I do this?
Do you actually program as you stated above:
1. Unprotect sheet
2. Clear cells
3. Protect sheet
That seems kind of weird.

Thanks!

OK, here's the part I really don't get.

I just went back to the spreadsheet.
I went to the range that I tried to clear with the procedure.

Remember, the procedure that wouldn't clear the cells, but instead gave a pop-up saying the sheet is protected, yada yada....?

Well guess what? That's right.
I had no problems clearing the cells manually!
I just selected all the cells...hit delete... and they were all gone!!!

So much for a protected sheet!

?????

Originally Posted by ChrisXcel

Why would you lock a cell if a sheet is unprotected then?

You wouldn't. Note, however, that cells are locked by default. If you want to manage cells through locking/protection, you need to unlock the ones where you want to allow user input, then protect the sheet.

Do you actually program as you stated above:
1. Unprotect sheet
2. Clear cells
3. Protect sheet
That seems kind of weird.

Not sure why you think it's weird. If you want to clear a range that contains locked cells, and the sheet is protected, you have unprotect the sheet first. It's true if you are simply using Excel, and it's true if you write code to do it.

Originally Posted by 6StringJazzer

Not sure why you think it's weird. If you want to clear a range that contains locked cells, and the sheet is protected, you have unprotect the sheet first. It's true if you are simply using Excel, and it's true if you write code to do it.

Further to my problems with cell locking/protection.

I have protected my worksheet and have entered worksheet protection code to several of my subs.

Just now I was working on this worksheet, in a cell.
I entered values and copied a formula to the cell. No problem.

Then all of a sudden, the next time I tried to enter something in the cell Excel complains that the worksheet is protected!
WTF!

This is why I've never been able to get a handle on locking/protection.
Because Excel is so inconsistent with its handling of the process.

I remember what I did the last time a few years ago.
I just threw my hands in the air and said "OK, I give up!"

If this continues I think I will do it again.

Any suggestions?

I need to see your code. Can you attach your file? It is possible that your code is doing something that you are not expecting it to do.

Also, take what you described in your last post and provide enough detail so that I can reproduce those exact actions on your file, so I can see exactly the same behavior that you are seeing. Include how you protected the sheet, exactly what cells you are pasting data into, and your "Paste Special" sequence (there are many options under Paste Special).

Originally Posted by 6StringJazzer

I need to see your code. Can you attach your file? It is possible that your code is doing something that you are not expecting it to do.

Also, take what you described in your last post and provide enough detail so that I can reproduce those exact actions on your file, so I can see exactly the same behavior that you are seeing. Include how you protected the sheet, exactly what cells you are pasting data into, and your "Paste Special" sequence (there are many options under Paste Special).

Thanks 6String for your help.

As I was preparing the file to upload here (I trimmed out unnecessary data) I came upon a bunch of cells in cols A, B, C, and D that were not allowing me to delete data entered.
There were 8 cells in total in these columns.

I searched the net on how to find locked cells in a worksheet.
I followed the procedure and discovered that the 8 cells found were the 8 cells that I couldn't delete data from.

Then I searched the net for info on how to unlock these cells.
I unlocked them and protected the sheet and tried to enter and delete data in these
8 cells.

I was now able to delete data I entered in the cells.
This was a big eye-opener for me and I now understand a lot more about
locking/protection than I did just an hour ago.

I hope this will stop the errors I was getting when running some procedures.
I may even put back protection in my worksheet (I gave up on it last night).

Although at the moment, I can't remember why I implemented it in the first place.

More puzzlement and mystery...

What I had done above, was take my main workbook file, delete all the worksheets in it except for the first one where my main data lay.

Then I re-named the file and saved it. After that I searched for locked cells and found the 8 that I mentioned.

Just now I opened up the original .xls file from which that sheet came from.
I wanted to clean it up and get rid of the 8 locked cells in the main worksheet.
Guess what? There were no locked cells in the worksheet!

Can someone please tell me what the heck is going on here?