Automatic date entry

[daveray]

Is there a way to automatically enter a date into a worksheet. I have a sheet with the date in coloumn D. What I would like is a way of the date being entered as soon as a value is entered into column A.

Unfortunately, I also need that date to stay permanently even if the original entry in A is edited at a later date.

For example a salesman could bring a deal into the office and it could be logges in the sheet. If that salesman leaves or if another salesman amends the deal resulting in a different name being entered into A I don't want the original date in D to change.

Is it possible?

Dave

 

[Jack in the UK]

Hi --

Do you require Code VBA or formula

 

[daveray]

Either would do Jack.

VBA would be interesting as I am trying to get into that more.

Thanks in anticipation.

Dave

 

[NateO]

You could right click on the sheet in question, click view code and enter the following vba:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(1)) Is Nothing Then
    If Target.Offset(, 3).Value = Empty _
    And Target <> "" Then
    Target.Offset(, 3).Value = Date
    End If
End If
End Sub

You must make sure your events are enabled. Hope this helps.

 

[webking]

In the Worksheet module for the concerned sheet, insert the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Col = Target.Column
Dim Temp As Range
If Col = 1 Then
Set Temp = Target.Offset(0, 3) 'For col. D:D
If IsEmpty(Temp) Then Temp = Now()
Temp.NumberFormat = "mmm dd, yyyy" 'Or whatever date format you like
End If
End Sub


or the other way can be:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
If Target.Offset(0, 3).Text = "" Then
Target.Offset(0, 3).Value = Now
End If
End If
End Sub

 

[Dave Hawley]

So many ways to skin a cat )

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Not IsDate(Target(1, 4)) Then
Target.Cells(1, 4) = Date
End If
End Sub