protecting cells in dependency of other cells

[Mario]

HI!
i have a problem in reference to protecting cells:

my 2 cells A1 and A2
i want that A2 is as long protected as A1 is empty, so that the user has to
fill in A1 first and finally then he can fill out A2.
so i want A2 to be write protected as long as A1 is empty

maybe some of u has a solution that could help me

regards
mario

 

[J.E. McGimpsey]

You'll want to use an event macro:

First, Unlock A1 and any other cells you want available to the user
(Format/Cells/Protection). Protect the sheet using
Tools/Protection/Protect Sheet. Put this in the worksheet code
module (right-click on the worksheet tab, choose View Code, paste
the code in the window that opens):


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const PWORD As String = "drowssap"
If Target.Address(0, 0) = "A1" Then
Me.Unprotect Password:=PWORD
With Target.Offset(1, 0)
If IsEmpty(Target.Value) Then
Application.EnableEvents = False
.Clear
Application.EnableEvents = True
.Locked = True
Else
.Locked = False
End If
End With
Me.Protect Password:=PWORD
End If
End Sub

Change the password to the one you picked. You'll also need to
protect your VBA project (in the VB Editor: Tools/VBAProject
Properties.../Protection Tab).