Use a function to turn cell protection on or off

[vtentarelli]

Is there a function to turn the cell protection on or off? For example in the attached spreadsheet, data is entered in the yellow-colored, unprotected cells in the top table. Calculations are recorded in the orange, protected cells. As a lazy way to avoid having to repeat data entry in the second table, there is a check box that controls whether the values in the first table are copied to the second table. If the box is unchecked (and cell B23 is "False") the analyst will manually enter the new set of data in the second table. For this reason the cells must remain unprotected to allow data entry when the spreadsheet is protected. Is there a way/function to turn on the cell protection when the checkbox is checked (B23=TRUE) and turn off the cell protection when the box is unchecked (B23=False). I tried using the function CELL but that just informs you of the condition of the cell. It does not control the cell format.

[gmr4evr1]

Take a look at this. It kind works, other than throwing up a message each time you click the checkbox..
vtentarelli-use-a-function-to-turn-cell-protection-on-or-off-11-20-2017-control-cell-protection.xlsm

[Richard Buttrey]

Hi,

Just a semantic correction. Cells aren't protected they are either locked or not. A sheet is said to be protected or not. That said I understand what you're getting at.

One way would be to use the Click Event of the Checkbox. Name the cells that you want to be toggled between Locked or not as say "MyRange". Then use this macro

Please Login or Register  to view this content.

[vtentarelli]

I did not receive the results I expected with your macro, but it could be because I am not very familiar with macros. I named the sequence of cells, "MyRange" as you instructed. I then selected the Developer tab and clicked on the Design Mode icon. I placed my curser on the check box and right clicked to view the selections in the pop-up window. I selected "View Code" then pasted your macro in the open window with the heading CheckBox1/Click. I closed the window and ended the Design Mode session. I clicked the box and the entries in the top table appeared in the bottom table. However, I was able to edit those cells included in MyRange. Was there an error in my steps? Do I have to protect the worksheet for this macro to work? If I protect the worksheet and click the checkbox, I get a Visual Basic Error message: "Unable to set the Locked property of the Range class. If I select Debug the following line of code is highlighted in yellow: : Range("MyRange").Cells.Locked = True. I have a feeling I'm missing some critical step. Any ideas?

[Richard Buttrey]

Hi,

Yes, I sort of assumed that you would be protecting the worksheet since that's the only way the locked statis of cells becomes operative. Think of worksheet prorection and cell locking like a field with an electric fence around it. The fence is useless unless yuo turn on the electricity.

There was no need to use the macro recorder you could just have copied the code I gave you into the Click event of the control.

Try adding the line

Sheet1.Unprotect immediatel after the IF line and finish the macro with the Sheet1.Protect instruction

[vtentarelli]

Once I corrected my typo, the code worked well. Another step was for me to figure out how to include two disjointed group of cells into the macro. I wasn't very successful with naming a range, so I just used the cell references instead. Once properly installed, I received no more error messages and got the results I wanted. Thanks again.

[Richard Buttrey]

Hi

Just select all the cells you want to lock and then add the name in the name box.