Keeping a Date in Excel

[Richie]

Hello folks hope someone can help me.

In Excel 2000 I have an Event in Column E to put the
current date in a cell whenever something is entered into
column b. But if 2 days later I make an adjustment in
column B the date will change in Col. E. I don'r want this
to occur. I need the original date to stay there in Col E.
The Event I use is

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 2 Then Exit Sub
If .Column = 2 Then
With .Offset(0, 3)
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
End If
End With
End Sub

Is there something I can add to this so the date will
never change in COL E due to an entry in COL B. Thanks for
the help.

Richie

 

[Ron de Bruin]

You can check if there is something in the cell

Try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 2 Then Exit Sub
If .Column = 2 Then
With .Offset(0, 3)
If Len(.Value) = 0 Then
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End If
End With
End If
End With
End Sub

 

[Gord Dibben]

Richie

Check for a value in E. If there is one, do not change it.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 2 Then Exit Sub
If .Column = 2 Then
If .Offset(0, 3).Value = "" Then
With .Offset(0, 3)
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
End If
End If
End With
End Sub

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[Richie]

-----Original Message-----
You can check if there is something in the cell

Try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 2 Then Exit Sub
If .Column = 2 Then
With .Offset(0, 3)
If Len(.Value) = 0 Then
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End If
End With
End If
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)







.
Thank you gentlemen