Automatic Static Date when Data is entered into another cell.

Question

I would like to have A1 automatically insert the date and time, when someone enters data into B1.  I do not want this date/time to ever change.

Answer 1

Is it possible to enter the date into A2 when the data is entered into C7?  Or to have the date enter into A2 when you first enter the document and put data into any field?  Rose

You have to make up your mind what you want to happen.

The following will enter the date/time in A2 when anything is first entered in C7.  A2 will change next time C7 is changed.  If you want A2 to never change again, remove the single quote from this line in the code.

'  If Range("A2") <> "" Then GoTo ws_exit

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:

    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range("C7")) Is Nothing Then

    If Target.Value <> "" Then

'    If Range("A2") <> "" Then GoTo ws_exit        Range("A2").Value = Format(Now, "dd mmm yyyy hh:mm:ss")

    End If

    End If

ws_exit:

    Application.EnableEvents = True

End Sub

Gord

Answer 2

Is it possible to enter the date into A2 when the data is entered into C7?  Or to have the date enter into A2 when you first enter the document and put data into any field?  Rose

Answer 3

Thanks Gord, that worked perfectly.  Perhaps you can help with another question.  I need to be able to use the date in A1 and combine it with A2 and A3.  A2 has a model number and A3 has a serial number.  Currently I was doing A2&A3 which worked but when I use A1 with the date i.e. A1&A2&A3, the date does not come out write.  Is there a way to save the date / time as text in A1.  Rose

In a cell enter  =A2 & " " & A3 & " " &TEXT(A1,"mmm/dd/yyyy hh:mm:ss")

Gord

Answer 4

Thanks Gord, that worked perfectly.  Perhaps you can help with another question.  I need to be able to use the date in A1 and combine it with A2 and A3.  A2 has a model number and A3 has a serial number.  Currently I was doing A2&A3 which worked but when I use A1 with the date i.e. A1&A2&A3, the date does not come out write.  Is there a way to save the date / time as text in A1.  Rose

Answer 5

I would like to have A1 automatically insert the date and time, when someone enters data into B1.  I do not want this date/time to ever change.

This sheet event code will operate on B1:B100.  Adjust to suit.

The date entered in Ax will not change even if Bx is edited later.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:

    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range("B1:B100")) Is Nothing Then

        With Target

            If .Offset(0, -1) <> "" Then GoTo ws_exit

            If .Value <> "" Then

                .Offset(0, -1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")

            End If

        End With

    End If

ws_exit:

    Application.EnableEvents = True

End Sub

Gord