+ Reply to Thread
Results 1 to 15 of 15

Automatic date entry in cell?

  1. #1
    Registered User
    Join Date
    07-07-2008
    Location
    Chicago
    Posts
    17

    Automatic date entry in cell?

    I have a workbook with several sheets, each of which represents a client. At the bottom of this sheet, I have a call log to record the times that we attempted communication, left a message, etc. Since it's tedious enough as it is, I'd like to save my team 2 seconds and a slight bit of that tedium by having the date an attempt was made automatically recorded in the row where the attempt is entered.

    Let's say this all takes place in A47:D47. For example, if I called the client on 7/15/08 and left a message, I would record who was called in B47, the name of the caller (me) in C47, and the result in D47. If possible, I would like to have the date of the attempt (today's date on the given day) entered in A47 upon the entry of information in B47:D47.

    Let me know if that's not clear, or possible. THANKS.

  2. #2
    Registered User
    Join Date
    07-09-2008
    Location
    UK
    Posts
    20
    I know this is quite crude and I'm sure it could be improved, but if you have a function like:

    Please Login or Register  to view this content.
    Then as long as there is data in all three cells, the date will be displayed in the cell (you could put it in cell D47).

    If you wanted the date and the time to be displayed then you could replace =TODAY() with =NOW().

    I hope this is a start for you

    Alex

  3. #3
    Registered User
    Join Date
    07-07-2008
    Location
    Chicago
    Posts
    17

    Did the trick.

    "Crude" only because it isn't sweet-looking VBA, but that's exactly what I wanted. Never knew about the "NOW" function! Thanks a lot!

  4. #4
    Registered User
    Join Date
    07-07-2008
    Location
    Chicago
    Posts
    17

    WAIT -- not so fast...

    I guess it didn't quite work now that I look at it. So, the date and time will update to the current d/t, as opposed to the d/t the info was entered. For example, I inputted info into three different rows at three different times, and right now all of the d/t cells I tested this in turned to 9:29AM.

    Any ideas for how to freeze the d/t at the time of entry?

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    The TODAY() & NOW() Functions are volatile & will change whenever the workbook calculates.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Registered User
    Join Date
    07-09-2008
    Location
    UK
    Posts
    20
    yeah, of course, i didnt think about that. I had just considered that it would update as it was entered... not too sure how to get the time to almost 'imprint' itself on the worksheet. I will have a think...!

  7. #7
    Registered User
    Join Date
    07-07-2008
    Location
    Chicago
    Posts
    17

    Right.

    ...and I just found out the Microsoft help says as much - should have checked that first. Sure enough, any time I make changes to any part of the sheet, the date/time refreshes.

    Anyone have other ideas for how to "stamp" the d/t at the point of data entry without having it refresh like that?

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You will need VBA. Use the WorkSheet_Change event

    Please Login or Register  to view this content.
    Right Click on the sheet tab
    Choose "view code"
    In the VB Editor set the left hand drop down to WorkSheet
    In the VB Editor set the right hand drop down to Change
    Paste in the code

  9. #9
    Registered User
    Join Date
    07-07-2008
    Location
    Chicago
    Posts
    17

    Question before I test this...

    ...I already have a bit of code titled "Worksheet_Change" in each sheet. Can I just rename either of these code snippets to something else?

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You will have the code procedure in each sheet, are you actually using it with code in there? If you are then you will need to amend the code.

    Note: there's a typo in the original code

    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    07-07-2008
    Location
    Chicago
    Posts
    17

    Not quite

    First of all, I really appreciate all of your help with this. Unfortunately, this bit of code didn't have any effect.

    Correct me if I'm wrong, but it looks like this code doesn't deal specifically with the range of cells I'm entering the names/contacts' info into, but of course with my limited experience I'm not sure. I might be able to figure it out if you explained what the different lines in the code are doing.

    So, just to be a bit more explicit, the "call log" this date entry would be a part of is in the lower third of the sheet, in cells A47:K63 (the top 2/3, rows 3 to 46, is basically a list of tasks that we're making sure clients are performing, with a status indicator and notes next to each). The columns in the range are labeled as follows (every other column is a spacer column, and there are some merged cells, hence the skipping):

    A: Date.
    C: Person Contacted. A drop-down menu appears when the cells in this column are clicked, with the contact to be chosen from a list.
    G: Caller. Selected from a list that appears in a drop down menu when clicked.
    I: Notes. User can type comments here.

    So, I'd like to have the date appear in its respective row when C47:63 or G47:63 are updated. It doesn't necessarily have to be both together, but I think in most cases users, will enter both a Person Contacted and a Caller.

    Sorry to be long-winded. Thanks again for all of your assistance.

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Can you attach a zipped workbook with your layout, with any confidential data deleted? I will then be able to see better what you need.

  13. #13
    Registered User
    Join Date
    07-07-2008
    Location
    Chicago
    Posts
    17

    Attached.

    FYI, there are seven other sheets in this book. If I need to paste whatever code into each individual worksheet's code, that's fine, but let me know if it's possible to paste this into the code for the entire book.
    Attached Files Attached Files

  14. #14
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Try this, I have combined the two worksheet codes. My addition adds the date 7 time & user name automatically.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-07-2008
    Location
    Chicago
    Posts
    17

    Nice.

    This will do it. Thanks a ton.

+ 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