Closed Thread
Results 1 to 18 of 18

automatic date stamp if a cell value changes

  1. #1
    Registered User
    Join Date
    02-03-2010
    Location
    Nokomis, Florida, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    automatic date stamp if a cell value changes

    I am trying to automatically capture and record the date of a cell's last change in value (date stamp). I have an item price list and if a particular cell gets updated I want to automatically record the date of change of that cell. I realize that after I change cell A1 I could tab to cell B1 and enter Ctrl+; but if I have a hundred new prices to enter I don't want to do that (plus me or the data entry person might forget).

    For instance,

    A B
    1 $32.17 8/17/08
    2 $43.20 9/12/09

    If I go in and update cell A1 to $33.98 today I would like cell B1 to read 2/16/10. But I don't want cell B2 to change.


    Thanks
    Last edited by dockdude; 02-16-2010 at 03:54 PM.
    -dockdude

  2. #2
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: automatic date stamp if a cell value changes

    Right click on the tab where you would like this to happen and choose View Code
    Paste the following

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    02-03-2010
    Location
    Nokomis, Florida, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: automatic date stamp if a cell value changes

    I tried that code and it doesn't seem to work. I should add that I haven't really ever used VBA.

    Are there any other ideas or could someone help me through the programming?

  4. #4
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: automatic date stamp if a cell value changes

    Quote Originally Posted by dockdude View Post
    I tried that code and it doesn't seem to work. I should add that I haven't really ever used VBA.

    Are there any other ideas or could someone help me through the programming?
    It what way does it not work?
    I've included how to add the code to the sheet module in my last post.
    Attached is a sample workbook that includes the code on Sheet1.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-03-2010
    Location
    Nokomis, Florida, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: automatic date stamp if a cell value changes

    thanks for your patience.

    When I tried to add it to my sheet I
    1) right clicked on the sheet name and selected View Code
    2) copy and pasted the code you supplied and deleted the first and last lines of the code it automatically puts in there (Sub and End Sub).
    3) typed in a value in A1
    4) typed =Worksheet_Change(A1) in cell B1
    5) got a #NAME? error in B1
    6) changed the value in A1
    7) got no change in B1

    I tried that in your excel workbook and didn't have any different results.

  6. #6
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: automatic date stamp if a cell value changes

    1) right clicked on the sheet name and selected View Code OK
    2) copy and pasted the code you supplied and deleted the first and last lines of the code it automatically puts in there (Sub and End Sub). The code should be pasted exactly as it appears. There is no need to delete anything
    3) typed in a value in A1 OK
    4) typed =Worksheet_Change(A1) in cell B1 This is not a function, it is a Worksheet event
    5) got a #NAME? error in B1 See comment for 4
    6) changed the value in A1
    7) got no change in B1 This is because you changed the code

    Take a look at the workbook I uploaded with my last post. Do not change anything in the code.
    Last edited by mdbct; 02-16-2010 at 03:29 PM.

  7. #7
    Registered User
    Join Date
    02-03-2010
    Location
    Nokomis, Florida, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: automatic date stamp if a cell value changes

    The code looks exactly as it does in your post.

    I'm not familiar with Worksheet Events.

    But I have a feeling I didn't make my first post clear enough.

    I'm not looking to show the entire worksheet has had a change made to it. I am only trying to demark that a particular cell has had a change and what that date was. I plan on using that information to let the end user know whether the prices that they are using are current.

    For example:


    Item Qty Price Each Last Updated
    bottle of coke 2 $1.00 5/08/2009
    gallon of gas 10 $2.75 10/11/2009
    quart of oil 1 $ 3.25 12/15/2008

    This will then compute that the total bill is (2*1)+(10*2.75)+(1*3.25)=$32.75 but will let the user know that the pricing is not reliable because the price of gas is not current enough.

    I plan on putting time limits to alert the end user that the prices may be out of whack. So the prices for the coke might be good for 1 year, the price for the oil might be good for 1 1/2 years, but the price of gas may only be good for 1 week.

    Therefore when I go into the program and update the Price Each for gas it will automatically give me todays date for the Last Updated and then keep it there. But the Last Updated dates for the Coke and Oil will remain the same.

    So, based on that, is that what your program is supposed to do for me? If not, do you know how to get Excel to bend to my will?

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: automatic date stamp if a cell value changes

    What column will you be changing? I'm guessing C (Price each)? Mdbct's code is set up for what you asked for. It is looking for changes in Column A however. Change Range("a:a") to Range ("c:c"). Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Registered User
    Join Date
    02-03-2010
    Location
    Nokomis, Florida, USA
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: automatic date stamp if a cell value changes

    Eureka!

    It's always the subtle things that get me.

    Yes, now it works.

    I see my error.

    Thank you. Thank you. Thank you. (meant for both of you)

  10. #10
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: automatic date stamp if a cell value changes

    This line looks to see if the cell you are changing (Target) in this case, intersects with the A column.
    Please Login or Register  to view this content.
    If you change something in a column other than A, nothing else happens.
    This line inserts the Date into cell 0 rows down and 1 column to the right of the target.
    Please Login or Register  to view this content.


    Glad to see you've gotten it sorted out.

  11. #11
    Registered User
    Join Date
    02-16-2010
    Location
    Thaiand
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: automatic date stamp if a cell value changes

    Thanks also, This is very handy for me.

  12. #12
    Registered User
    Join Date
    02-16-2010
    Location
    Thaiand
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: automatic date stamp if a cell value changes

    One question: How would you modify this code, so that any change in a range of cells in that row would update the date in the date column. e.g. any change in A1, B1, C1, D1 or E1, would change date in F1.
    thx

  13. #13
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: automatic date stamp if a cell value changes

    Just change the range to "A:E" Make sense?

  14. #14
    Registered User
    Join Date
    02-16-2010
    Location
    Thaiand
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: automatic date stamp if a cell value changes

    Quote Originally Posted by ChemistB View Post
    Just change the range to "A:E" Make sense?
    Hi, I tried that, but if i change only coumn A, then the date is put into columns B, C, D, and E.

  15. #15
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: automatic date stamp if a cell value changes

    Ooops, sorry. Change the OFFSET row to
    Please Login or Register  to view this content.
    Does that work for you?

  16. #16
    Registered User
    Join Date
    02-16-2010
    Location
    Thaiand
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: automatic date stamp if a cell value changes

    WORKS GREAT. Thank You.

  17. #17
    Registered User
    Join Date
    03-18-2010
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: automatic date stamp if a cell value changes

    Thanks for posting this, it has helped a LOT

    I've had to modify the code a little bit to stop an error appearing when I insert a new Row:

    Please Login or Register  to view this content.
    On the first example you posted it all ran ok until until I attempted to insert a new Row, then it would put the date into about 5 of the cells to the right of where it should do and I received an error message with the usual Debug stuff on it. It would also delete my column descriptions that I had on Row 2.

    Would it be possible for it to not auto insert the date on any new row that I insert?
    Also, what would be the correct range for me to get the code to work on c3:infinity....

    Any help appreciated.

  18. #18
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: automatic date stamp if a cell value changes

    Welcome to the forum.

    Please take a few minutes to read the forum rules, and then start your own thread.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

Closed 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