Automatic date entry in cell?

**royUK** · 07-16-2008, 10:33 AM

The TODAY() & NOW() Functions are volatile & will change whenever the workbook calculates.

**mackinlay** · 07-16-2008, 10:35 AM

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...!

**mrs179** · 07-16-2008, 10:37 AM

...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?

**royUK** · 07-16-2008, 11:11 AM

You will need VBA. Use the WorkSheet_Change event

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Or Target.Column <> 2 Then Exit Sub
    Target.Offset(0, -1).Value = Date
    Target.Offset(0, 1).Value = Environ("username")
    Traget.Offset(0, 2).Select
End Sub

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

**mrs179** · 07-16-2008, 11:17 AM

...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?

**royUK** · 07-16-2008, 11:23 AM

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

Traget.Offset(0, 2).Select

should be

Target.Offset(0, 2).Select

**mrs179** · 07-16-2008, 12:18 PM

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.