Record the date when a record is added or modified using a user form

[Denny57]

I understand the formula to apply todays date to a cell is =TODAY(), however, I am looking to apply this date to individual records either when a new record is added or an existing record is modified.

I am hoping that there is a simple line of code that I can insert into the appropriate sub-routine so that the date is inserted when each record is added or updated.

Here is my code for adding a new record. The date should be applied to Column L. GThe highlighted line of code

Private Sub cmdAddRecord_Click()
'Used to add new records to the Preserved Locomotives Database
Dim lastrow As Long
Dim LocomotiveNumber As String
StockNumber = txtBritishRailwaysNumber

If Application.WorksheetFunction.CountIf(Sheets("Preserved Steam Locomotives").Range("D3:D1500"), LocomotiveNumber) > 0 Then

MsgBox "Locomotive Number Already Exists", 0, "Duplication Check"

Call UserForm_Initialize
txtLocomotiveClass.SetFocus

Exit Sub

End If

lastrow = Sheets("Preserved Steam Locomotives").Range("A" & Rows.Count).End(xlUp).Row
Cells(lastrow + 1, "A").Value = txtRailwayCompany
Cells(lastrow + 1, "B").Value = txtLocomotiveClass
Cells(lastrow + 1, "C").Value = txtLocomotiveClassName
Cells(lastrow + 1, "D").Value = txtBuildDates
Cells(lastrow + 1, "E").Value = txtBritishRailwaysNumber
Cells(lastrow + 1, "F").Value = txtRailwayCompanyNumber
Cells(lastrow + 1, "G").Value = txtOtherPreviousNumbers
Cells(lastrow + 1, "H").Value = txtLocomotiveName
Cells(lastrow + 1, "I").Value = txtCurrentLocation
Cells(lastrow + 1, "J").Value = DTPicker3
Cells(lastrow + 1, "K").Value = txtAdditionalInformation

Cells(lastrow + 1, "L").Value = TODAY()

MsgBox txtBritishRailwaysNumber & " has been added to the Preserved Steam Locomotive database", 0, "Locomotive Number Added"

Call UserForm_Initialize
txtLocomotiveClass.SetFocus

End Sub

Hopefully there is a simple solution to add a formula to apply the "action" date only when the corresponding record is added or modified once the message box has been clicked "Yes" and this date should not change until the record is subsequently modified.

 

[Denny57]

I understand the formula to apply todays date to a cell is =TODAY(), however, I am looking to apply this date to individual records either when a new record is added or an existing record is modified.

I am hoping that there is a simple line of code that I can insert into the appropriate sub-routine so that the date is inserted when each record is added or updated.

Here is my code for adding a new record. The date should be applied to Column L. GThe highlighted line of code

Private Sub cmdAddRecord_Click()
'Used to add new records to the Preserved Locomotives Database
Dim lastrow As Long
Dim LocomotiveNumber As String
StockNumber = txtBritishRailwaysNumber

If Application.WorksheetFunction.CountIf(Sheets("Preserved Steam Locomotives").Range("D3:D1500"), LocomotiveNumber) > 0 Then

MsgBox "Locomotive Number Already Exists", 0, "Duplication Check"

Call UserForm_Initialize
txtLocomotiveClass.SetFocus

Exit Sub

End If

lastrow = Sheets("Preserved Steam Locomotives").Range("A" & Rows.Count).End(xlUp).Row
Cells(lastrow + 1, "A").Value = txtRailwayCompany
Cells(lastrow + 1, "B").Value = txtLocomotiveClass
Cells(lastrow + 1, "C").Value = txtLocomotiveClassName
Cells(lastrow + 1, "D").Value = txtBuildDates
Cells(lastrow + 1, "E").Value = txtBritishRailwaysNumber
Cells(lastrow + 1, "F").Value = txtRailwayCompanyNumber
Cells(lastrow + 1, "G").Value = txtOtherPreviousNumbers
Cells(lastrow + 1, "H").Value = txtLocomotiveName
Cells(lastrow + 1, "I").Value = txtCurrentLocation
Cells(lastrow + 1, "J").Value = DTPicker3
Cells(lastrow + 1, "K").Value = txtAdditionalInformation

Cells(lastrow + 1, "L").Value = TODAY()

MsgBox txtBritishRailwaysNumber & " has been added to the Preserved Steam Locomotive database", 0, "Locomotive Number Added"

Call UserForm_Initialize
txtLocomotiveClass.SetFocus

End Sub

Hopefully there is a simple solution to add a formula to apply the "action" date only when the corresponding record is added or modified once the message box has been clicked "Yes" and this date should not change until the record is subsequently modified.

Resolved

 

[smozgur]

Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[Denny57]

Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

Here is the solution

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("A:A").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.Row, "L").Value = Date
        Else
            Cells(Cell.Row, "L").Value = ""
        End If
    End If
Next Cell
End Sub

However I have a further problem which I will post under a new thread

 

[Peter_SSs]

One further request: When posting vba code in the forum, please use the available code tags. My signature block below has more details. In post #4 have added them for you this time. ?