Inserting the current date automatically in a cell when filling in a data sheet

[Andyk8ley]

I need to have a date cell (D2) automatically filled in with the date on which that row was completed. If the data capturer filles in the client details in another cell (F2), I would like to have the date permanently input into cell D2.

Please assist

NUM	CONFIRMATION AGENT INITIALS	PLEDGE AGENT INITIALS	INITIAL DATE DATE YYMMDD	LANGUAGE	PHONE (CELL)
1		LR	150401	English	0833836255

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

Thanks

 

[JLGWhiz]

This is event code and runs when changes are made to the worksheet in which the code resides.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
	If Not Intersect(Target, Range("F2")) Is Nothing Then
		Range("D2") = Format(Date, "yymmdd")
	End If
Application.EnableEvents = True
End Sub

To install the code, right click the name tab of tha applicable worksheet, then click 'View Code' in the pop up menu. This will open the code window for that sheet. Copy the code into the large pane of the VB editor and save the workbook as a macro enabled workboo, if not already so. Close the VB editor and the code will now execute if data is added to cell F2.

 

[etaf]

you can use a timestamp
heres a link to a couple of tutorials on how to do that
Create A Timestamp In Excel With Formulas
How to insert timestamps in excel sheet using formulas | Chandoo.org - Learn Microsoft Excel Online

 

[npvrader]

JLGWHIZ,
I realize his posted example was for a single row, but if he had multiple rows he would have to specify Range("F2:Fxx") or he could refer to a named range. Here is my question, can the event use an expandable named range specifically where the user has defined a dynamic range in the name manager?

 

[JLGWhiz]

JLGWHIZ,
I realize his posted example was for a single row, but if he had multiple rows he would have to specify Range("F2:Fxx") or he could refer to a named range. Here is my question, can the event use an expandable named range specifically where the user has defined a dynamic range in the name manager?

If the user wanted to check for any entry into column F, beginning at row 2 and down, then have the transactiondate entered in column D then

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Not Intersect(Target, Range("F2", Cells(Rows.Count, "F").End(xlUp))) Is Nothing Then
        Target.Offset(0, -2) = Format(Date, "yymmdd")
    End If
Application.EnableEvents = True
End Sub

You can use any range, so long as it is defined properly in the code.

 

[npvrader]

Thanks JLGWHIZ!

 

[Andyk8ley]

NUM	CONFIRMATION AGENT INITIALS	PLEDGE AGENT INITIALS	INITIAL DATE YYMMDD	LANGUAGE	PHONE (CELL)
1		LR	150401	English	0833836255

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

Ok, so I am a little lost here as a beginner. I had a rethink and when the Pledge Agent enters their initials in a cell in column C, I would like the date to appear in a cell in column D. I have 15000 rows of data and this will grow. I would also lock the cells in column D so the date can't be changed.

I tried the previous VBA from JLGWhiz but it wouldn't work. I am running office 2010

Thanks for the guidance so far.

If the user wanted to check for any entry into column F, beginning at row 2 and down, then have the transactiondate entered in column D then

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Not Intersect(Target, Range("F2", Cells(Rows.Count, "F").End(xlUp))) Is Nothing Then
        Target.Offset(0, -2) = Format(Date, "yymmdd")
    End If
Application.EnableEvents = True
End Sub

You can use any range, so long as it is defined properly in the code.

 

[JLGWhiz]

See if this will work for you.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Not Intersect(Target, Range("C2", Cells(Rows.Count, "C").End(xlUp))) Is Nothing Then
    ActiveSheet.Unprotect
    Cells.Locked = False
        Target.Offset(0, 1) = Format(Date, "yymmdd")
    Columns("D").Locked = True
    ActiveSheet.Protect
    End If
Application.EnableEvents = True
End Sub

 

[Andyk8ley]

Thanks , it works. However I am getting a date as follows

Should show as 150524

Col C Col D

AK

120213

<tbody>
</tbody>

 

[JLGWhiz]

I don't know why it would do that. 'Date' returns the current date in system date format, so the code should return 150524 for todays date. It does that on my system.