conditional cell protection

[ijw]

Is it possible to conditionally protect a cell or range of cells?
Let's say that;

A1 is empty - A2 has 2.81.
Both cells are formated as unprotected. The spreadsheet is protected.
I need to change the contents of A2 regularly.

Until,
I enter a date into A1. Then I want A2 to become protected so that it
cannot be accidentally altered.

Possible???

 

[Ardus Petus]

Possible with VBA event procedure (macro)

HTH

 

[JE McGimpsey]

One way:

Put this in the worksheet code module (right-click the worksheet tab and
choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPWORD As String = "drowssap"
If Not Intersect(Target, Range("A1")) Is Nothing Then
If IsDate(Range("A1").Value) Then
Me.Unprotect Password:=sPWORD
Range("A2").Locked = True
Me.Protect Password:=sPWORD
End If
End If
End Sub

 

[ijw]

Thanks JE Mc,

The code does the trick, which is great - just what I wanted.

However it leaves the whole spreadsheet password protected. I don't
normally password protect my worksheets as I am the only one using
them. Can this be changed?

Ian