A cell's value renders a workbook read-only

[mrlanier]

Can a workbook be rendered "read-only" if Sheet1, A1 returns a value of
1 (A1=1)? In like manner, can the workbook return to normal use if the
cell's value is changed to 0 (A1=0)? Indeed, can the cell's value even
be changed to 0 if the workbook is "read-only"? If it can, let's
assume an entry in B1 of any value greater than 0 will cause A1=0.
Thanks.

Michael

 

[Guest]

OK, Here's what you can do.

Go to Visual Basic
Tools -> Macro -> Visual Basic Editor

And create the following:

Sub Macro1()
'
' Macro1
'

'
If (Range("A1") = 1) Then
Range("A1").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveWorkbook.Protect Structure:=True, Windows:=False
ElseIf (Range("A1") = 2) Then
ActiveWorkbook.Unprotect
End If
End Sub

This will lock the worksheet, but allow you to still edit cell A1.

Now, create a box from your rawing toolbar. Right-click on the box, and
click Assign Macro...

Assign Macro 1 to the box.

Now, when you make cell A1 1, click on the box, and your sheet will be
protected. Change A1 to 2 and click on the box, your sheet will be
unprotected.

Hope this helps...

 

[mrlanier]

Sean,

Is it possible to place the elements of this macro within the already
existing macro I am using in Sheet1 by pasting the range under Private
Sub... as follows?

Private Sub Worksheet_Change(ByVal TargetCell As Range)
If (Range("A1") = 1) Then
Range("A1").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveWorkbook.Protect Structure:=True, Windows:=False
ElseIf (Range("A1") = 2) Then
ActiveWorkbook.Unprotect
End If
End Sub

Also, if possible, I need to avoid the use of a button to execute the
macro. Perhaps the macro can be executed as an entry is entered in the
worksheet. Can this work?

Michael