Locking Sheet based on Cell Value for a Macro

I am using a checkbox (forms not activeX). The checkbox is linked to cell A1, so A1 is True when the checkbox is checked and false when the checkbox is not checked.

I want the sheet to be locked/protected when A1 is TRUE and when A1 is false have the sheet unlocked/unprotected.

Any suggestions on how to achieve this?

First, you do not link this checkbox to a cell because when the sheet will be protected, you'll get an error because you are trying to write to cell A1. So the macro will make the link by programming. See macro below. I attatch a workbook as an working example.

Please Login or Register  to view this content.

If I want the entire sheet protected at all times except for lets say cells A3 to A8. So if the checkbox is checked no cells in the entire sheet can be edited. If the checkbox is unchecked only cells A3 to A8 can be edited. That is no data can be entered into the sheet except cells A3 to A8. Below is what I have this far from the previous program. I am new to VBA so not sure on the format but I can not get it to work.

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes("Check Box 1") = xlOn Then

Range("A3:A8").Select
Selection.Locked = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Else

Range("A3:A8").Select
Selection.Locked = False
Selection.Unprotect

End If

End Sub

First of all, you'll have to review forum rules because your last post do no comply with rule #3 asking you to use code tags to enclosed your codes.

Now, find below the answer to your question: In fact, you want the sheet to be protected all the time except for some cells that you want to unlock when the checkbox is checked. So here is the code to do that:

Please Login or Register  to view this content.

In the attached file, you'll find a working workbook.
regards
Pierre