+ Reply to Thread
Results 1 to 16 of 16

Insert automatic date and time stamp in a cell if any cell of the same row is edited

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    Dubai UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Unhappy Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Hello everyone,

    I am doing this excel sheet wherein if I edit or add a value to any of the cells in a particular row I would like an automatic date time stamp inserted in a certain cell of the same row. Please explain how to do I achieve above result?

    thanks



    Kaidezoher Ismail

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Use an event macro. Say, for example, data is entered in columns A thru K starting in the second row. We want an automatic date/time stamp in column L if data is either entered in a row or data is changed in a row. Insert the following macro in the worksheet code area:

    Please Login or Register  to view this content.

    Because it is worksheet code, it is very easy to install and automatic to use:

    1. right-click the tab name near the bottom of the Excel window
    2. select View Code - this brings up a VBE window
    3. paste the stuff in and close the VBE window

    If you have any concerns, first try it on a trial worksheet.

    If you save the workbook, the macro will be saved with it.


    To remove the macro:

    1. bring up the VBE windows as above
    2. clear the code out
    3. close the VBE window

    To learn more about macros in general, see:

    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    To learn more about Event Macros (worksheet code), see:

    http://www.mvps.org/dmcritchie/excel/event.htm

    Macros must be enabled for this to work!
    Gary's Student

  3. #3
    Registered User
    Join Date
    10-15-2012
    Location
    Dubai UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Thank you Jacobshavn,

    I appreciate your help. Your code is working perfectly. What happens if I add value in a cell within the range and if I choose to clear the same then how to make the stamp go away?

  4. #4
    Registered User
    Join Date
    10-15-2012
    Location
    Dubai UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Dear Jacobshavn,

    Also I need the code to stamp date/time if I copy paste to certain cells within range. Please be kind and reply. Thanks.

  5. #5
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    I will respond shortly..................

  6. #6
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    First remove the previous version
    Then install this version:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-15-2012
    Location
    Dubai UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Thanks a bunch Jacobshavn.

    I am grateful and appreciate your prompt response. Needless to say your code works perfect.

    Thanks again.

  8. #8
    Registered User
    Join Date
    10-15-2012
    Location
    Dubai UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Hello,

    The Time and Date stamp vanishes after I remove old values within the cell range (Even though the new data I entered is still there)?.
    Last edited by kdhanerawala; 11-03-2012 at 05:55 AM. Reason: Typo corrected and one line removed

  9. #9
    Registered User
    Join Date
    10-15-2012
    Location
    Dubai UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Please someone out there help!, thanks!!

  10. #10
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    The clearing of the date stamp is in accordance to your request:

    What happens if I add value in a cell within the range and if I choose to clear the same then how to make the stamp go away?

  11. #11
    Registered User
    Join Date
    10-15-2012
    Location
    Dubai UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Dear Jacobshavn,

    Thanks for responding, I note your point.

    Requesting the date/time to be stamped whenever I insert data or edit date for multiple cells within the range. The above happens perfectly.
    Now, when I remove old data (Not new) from cell/cells within the range the date stamp gets removed even though the newly inserted data exists.

    I need the date stamp to remain if I delete old existing data prior to new data insertion. I hope I articulated my problem clearly enough to be understood. Thanks again

  12. #12
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    683

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Since Jacobshavn is not online, have a try with these minor changes to his code:
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    10-15-2012
    Location
    Dubai UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Thanks for the above Rollis.. how does the time stamp go away after I remove the date I inserted? Is there a way to manually remove the time stamp?

    Thanks again!

  14. #14
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    683

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    This what you asked for in post #11:
    I need the date stamp to remain if I delete old existing data prior to new data insertion.
    When you clear all the input cells of a row the time stamp will automatically clear.
    To manually remove the time stamp just empty the cell, it will remain clear untill you fill in or change one of the input cells of that row.
    Last edited by rollis13; 11-11-2012 at 07:37 AM.

  15. #15
    Registered User
    Join Date
    10-15-2012
    Location
    Dubai UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    Thanks. If I clear the time stamp, it appears again. The same thing happens if I delete or insert a column.The time stamp shows even if there is no data in the new cells.

    Regards.

    I reworked the code, excluded some columns in the target range and now it seems to work as required..... fingers crossed. Thank you all for helping as ever.
    Last edited by kdhanerawala; 11-12-2012 at 04:04 AM.

  16. #16
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    683

    Re: Insert automatic date and time stamp in a cell if any cell of the same row is edited

    When you do maintenance to the sheet you should stop a macro that uses a Worksheet_Change.
    Anyway, glad being of some help but Jakobshavn did the dirty work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1