Insert New Row and Autofill Current Date (Not Time) In Column I

[mkdbee]

Hello There,

I have a report in Excel and am looking to track all items by their Log Date.</SPAN>

There are no dates yet in this column (I) since this is a new column added to my report.

I was hoping upon inserting a new row that the Log in Date would auto fill for users with the current date and not change if they open the report on the same day again to make changes to other columns in the same row with the current log in date </SPAN>

I’m thinking a VBA code----change event may work but the one I have tested out are not working. Any hints or suggestions?

Thank you!</SPAN>

 

[Namestaken]

Change events will work to detect a new row but it is kind of a convoluted process. The way I have done this is by creating a named range somewhere very low on the sheet and every time the change event is triggered, it checks the old position of the named range and the new position. In this way, you can detect that a new row was inserted but only if it is the entire row or the named range is in the row you are inserting a new row in.

If you need help implementing this (I'm not sure if you just needed some guidance or full coding help), let us know.

 

[phillfri]

Inserting a row triggers a worksheet_change event.
Firstly I'd make sure the report data is set up as an Excel structured table.
Then in the worksheet_change event try something like:

If Intersect(activecell.row, activesheet.listobjects(1).databodyrange then
If activesheet.cells(activecell.row,1) = 0 then
activesheet.cells(activecell.row,1) = Date
end if
end if

I'd format column 1 as a date format
That should get you headed to where you want to be.