Capturing a date field once only

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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 **
>
>
> --
> Rob Svilts
> ------------------------------------------------------------------------
> Rob Svilts's Profile:
http://www.excelforum.com/member.php...o&userid=28362
> View this thread: http://www.excelforum.com/showthread...hreadid=479423
>

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 **