Capturing a date field once only

[Rob Svilts]

I would like to capture a date once only.

Here's the scenario:

1) Cell A1 and cell A2 are blank
2) A user inputs a date in Cell A1. I want to capture that date in
cell A2
3) Cell A1 is later updated to a different date, but I want to keep
cell A2 as the original date

Manually, I'd 'copy', 'paste - options - overwrite formula', but how do
you do that from within a worksheet automatically.

Thanks.

** Rob **

 

[Bob Phillips]

Rob,

Try this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
With Target
If IsDate(.Value) Then
If .Offset(1, 0).Value = "" Then
.Offset(1, 0).Value = Format(.Value, "dd mmm yyyy")
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rob Svilts]

Hi Bob,
Many thanks for your quick reply post and fix. It works wonderfully.
I'm indebted to you.
All the best from the UK,
** Rob ** :