Protect/Unprotect Cells with VBA

I am creating a form in Excel and need some help.

I would like to lock cells depending on whether a check box is true or false. I wrote some code that accomplishes this, but when I protect the sheet, I get an error because the sheet is protected.

In essence, I only want a user to be able to enter data if a check box is true. If the check box is false, I want the cells to be locked.

I hope I explained that right.

Please Help.

Ross

Try something like this:

Code:

---

Option Explicit

Sub Macro1()
    ActiveSheet.Protect Password:="MyPass", UserInterFaceOnly:=True
    If ActiveSheet.CheckBox1.Value = True Then
        Range("A1:B5").Locked = False
    Else
        Range("A1:B5").Locked = True
    End If
End Sub

---

I am assuming you are using the ActiveX CheckBox. If you are using the Forms CheckBox the check the value of the Linked Cell.

Also the If statement can be reduced to this:

Code:

---

Range("A1:B5").Locked = Not ActiveSheet.CheckBox1.Value

---

So the error you are getting is when the user checks/unchecks the box, is that right? I assume that the code to toggle the "lock" on the cell.

Try doing this:

Code:

---

ActiveSheet.Unprotect
'your regular code goes here
ActiveSheet.Protect

---

The other way to do this would be to put a Workbook_Open procedure in, which loops through each require sheet, and runs something like this:

Code:

---

ActiveSheet.Protect userinterfaceonly:=True

---

This would stop users from hitting your sheets, but let's macros do their thing. The only problem (and reason for the workbook_open routine) is that this setting disappears when you close the workbook.

HTH,

Thanks to you both.

That worked Great!

Ross

You're Welcome :beerchug:

Take Care