Set Date in Locked Cell
 

Hi,

I am trying to auto-set the current date in a cell when data is modified in
a corresponding cell.

The cell that the date is being inserted into is locked so it works great
until I protect the sheet. Once the sheet is protected it will no longer
enter the date into the locked cell. How can I modify the code below to allow
it to enter the date even when the cell it is trying to enter it into is
locked?

"Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim R As Long
R = Target.Row

On Error GoTo ErrHandler
Application.EnableEvents = True

If Not Intersect(Range("L2:m20"), Target) Is Nothing Then
Range("L2:M20").Sort Key1:=Range("L2") '<<== CHECK RANGE
End If

If Target.Column < 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub

Target.Offset(0, 2).Value = Date

ErrHandler:
Application.EnableEvents = True
End Sub


"

Thanks,

Ben

Set Date in Locked Cell
 

That's as designed you can't enter data into a locked cell with a macro. You
need to unlock the sheet and then lock it back up after you are finished you
can do it all in the macro.
ActiveSheet.Unprotect Password:=Password
ActiveSheet.Protect Password:=Password, DrawingObjects:=True, Contents:=True
or similar
The only problem is that the password in stored in plain text but it is not
in plain sight... if you are a persistant / clever enough user you can find
it.
Hope that helps

"Ben Dummar" wrote:

Hi,

I am trying to auto-set the current date in a cell when data is modified in
a corresponding cell.

The cell that the date is being inserted into is locked so it works great
until I protect the sheet. Once the sheet is protected it will no longer
enter the date into the locked cell. How can I modify the code below to allow
it to enter the date even when the cell it is trying to enter it into is
locked?

"Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim R As Long
R = Target.Row

On Error GoTo ErrHandler
Application.EnableEvents = True

If Not Intersect(Range("L2:m20"), Target) Is Nothing Then
Range("L2:M20").Sort Key1:=Range("L2") '<<== CHECK RANGE
End If

If Target.Column < 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub

Target.Offset(0, 2).Value = Date

ErrHandler:
Application.EnableEvents = True
End Sub


"

Thanks,

Ben