Excel - Dynamic Timestamps, Auto Date Entered & Date Modified on Change (VBA)
Вставка
- Опубліковано 8 вер 2024
- Learn how to create an Excel VBA script to automatically add date entered and date modified timestamp columns to your data.
#excel #timestamp #vba #date
Exactly what I was looking for, thanks 😊
You're welcome 😊
Unbeatable! But please to get the similar efforts for google Sheets with compilation of all the major comments of this viewer
Amazing, It's exactly the need I have in my job.
Thanks,
Congrats for your job!
great!
Hello,
This was extremely helpful and got me halfway to figuring out the VBA code - there is a dynamic route that I am trying to figure out. What if we have a "Range" of columns that should someone change, the date modified would update as well(but its only that one column that would need to update realtime). To better explain, I have other Column Headers other than "notes", but the date modified offsets from my designated Date Modified Column. I need the Date Modified Column to remain static. Hope this makes sense.
Perfect content. Thank you!
I appreciate it!
Very well done video. Similar to what you present here, can you provide a screen print of codes for: Entry Date/Time Stamp; Modified Date/Time Stamp; but also Username of the person that entered the initial entry and modification entry? One other point of interest - on the same sheet, Sign-off Date/Time Stamp; Resign-off Date/Time Stamp; and Username for both here as well. Note, I tried created Entry Date/Time Stamp; Modified Date/Time Stamp and Sign-off Date/Time Stamp; Resign-off Date/Time Stamp myself, but the Entry input cell affects the Resign-off Stamp along with the Modified Date/time Stamp. Thanks!
Hey man, This video is great. The best I found on the topic. Would it be possible to do one using tables and referring to the table column name. I have been trying to do this and unable to.
ActiveSheet.ListObjects("myTable").ListColumns("Category").Range.Select
should select the column, so
ActiveSheet.ListObjects("myTable").ListColumns("Category").Range.Column
will most likely give you the right column number.
Thanks for the video. I have a question.
I want to add a time stamp whenever any changes are done on whole workbook. Can you please suugest how can we do that?
If you double click on the Workbook object in the editor and select it from the dropdown there is a SheetChange event
@@ExcelGoogleSheets Many thanks for the detailed explanation. But after selecting the SheetChange event what next, do we just copy the code or we need to reference each sheet? Please help throw more light on this.
@@enefiokjames205 did you find a solution?
Excelentes aportaciones, para uso de hoja de calculo. Gracias Learn Google Spreadsheets.
Gracias!
What if the notes column is the automatic column that update by the unique function from other table.
Thus, the cell will fill up without we are typing. Does the timestamp will calculate as well?
sir i am looking the formula for modified date time stamp regardless where cell is edited...
Hi ! Just a question, what if my headers starts at Row 7 ? What code do i have to include ?
I know that is in relevant question but it is possible to do "remove Spreadsheet version history i had checked all internet" through script.
can you send me this script? I tried copying you, but it doesn't work for me after I actuate certain steps.
Hii, Great Video man! What code can I use if "As soon as I enter my data, I want to fetch that same data from another worksheet and paste the value next to it in my current worksheet (in place of the timestamp)". I really need help on this
Hi Sir, I have 5 sheets in the excel, but i modified only 2 sheets on the particular day, but the remain 3 sheets are updated an week ago, so the 2 sheets which modified today should say the today's date, the remain 3 should show the last modified date only...so i will understand that which sheet when it has been modified, can you help to make the video on this please
Hi! :) How would you do a script where it keeps a record of the timestamps? Rather than just one updated time
Great coding. is there a way to do this with multiple headers on difrent row numbers?(16:58) "ColumnHeader = cells (x,x,x,x,x, target.column).value"
I don't understand what your headers looks like from your comment.
@@ExcelGoogleSheets My ColumnHeaders would be in Row 1, 10, 19,.... but with your coding that doesn't work. How would I make that work?
Hi If I use a Specific formula in Column A let the time stamp doesn't work could you please help
👍👍👍👍👍
Would someone be able to share a copy of this template/ file and /or the Final VBA code with me please? I think I missed a line or two and would appreciate it.
Can you send a link of the workbook?
Can I get the VBA code for this timestamp tutorial?
would you say that VBA is better then app scripts?
Depends on what you do. For reactive things like this, Excel being native desktop application, it works much faster, therefore you have better experience. With Google Sheets after every change you have to wait forever until it sends the change to the server, gets it done and then sends back the results, so you end up with laggy weird experience.
@@ExcelGoogleSheets I see what you mean, whenever I try to use the onEdit() on gas it always seems so laggy, I was really impressed by excel's speed. Thanks for the video man.
Hi thanks for this videio, could you write the entire formula/code for timestamp. It would be easier for me to look at the code as I review the video.
Same question. Sometimes, i cant follow up :(
iam not programmer
but i need the code written to add it plz
When I insert row I get application define or object define error. Is there a solution to this?
What your vba code?
@@ExcelGoogleSheets just saw your reply. Here is my code, the updatedColumn is my modifiedColumn if referencing. I'm trying to create a pass down log. another issue I'm having is deleting a row once the information has been viewed by everyone, this is the larger challenge than inserting. When I delete the row I get a timestamp in a new column, and two new additional columns are created in my table. Not sure if I'm explaining it well. New to VBA.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
columnlist = Array("Topic", "Followup", "Updated", "Initial", "Airport")
updatedColumnHeader = "Updated"
createdColumnHeader = "Created"
rowStart = 2
rowEnd = 100
headerRow = 1
numberOfColumnsToSearch = 10
columnHeader = Cells(headerRow, Target.Column).Value
isInlist = Application.Match(columnHeader, columnlist, 0)
If Not IsError(isInlist) And Target.Row >= rowStart And Target.Row
I'm getting error "compile error next without for in vba".. pls help
what's your code?
Did you ever figure out how to fix the next without for error? Still struggling here.