Protect ALL cells If Q

[John]

I want to protect all cells in a workbook if the Contents of cell B1 > 7. My
thinking is that if the value of Cell B1 > 7 then the worksheet should not
be worked on, kind of a security feature

Is there any such code I should use?

Thanks

 

[Tom Ogilvy]

How does B1 get to be >7. Manual entry? Formula? Macro?

 

[John]

Hi Tom

It will be a formula based on =(NOW())-A1, so if the user tries to work on
the form after 7 days, they wouldn't be allowed to. A1 will change based on
external data every week

 

[Tom Ogilvy]

right click on the sheet tab and select view code.

Paste in code like this:

Private Sub Worksheet_Calculate()
If Range("B1").Value > 7 Then
If ActiveSheet.ProtectContents = False Then
Me.Cells.Locked = True
Me.Protect
End If
End If
End Sub

 

[John]

Super Tom Thanks for that

right click on the sheet tab and select view code.

Paste in code like this:

Private Sub Worksheet_Calculate()
If Range("B1").Value > 7 Then
If ActiveSheet.ProtectContents = False Then
Me.Cells.Locked = True
Me.Protect
End If
End If
End Sub