Display the date and time each time the data in a cell range is changed

**BVZM** · 08-23-2012, 03:40 AM

I'm not familiar with macro's, but found one which accomplishes to change the date and time each time a cell in a column range is changed.

UDF:

Public Function UDF_Date(ByVal data) As Date
UDF_Date = Now()
End Function

Formula above range:
=UDF_Date(H7:H25)

Example:
H4 23/08/2012 09:00

H7 12
H8 40
...
...
H25 15

If any entry is made in cells H7 - H25, the date and time should be logged in cell H4.

However, when I open the excel sheet and enable macros it automatically changes the date and time to the date time the sheet is accessed. This also happens when I send the sheet via e-mail and the person on the other side opens it.

How can I correct this and embed the date with it only changing if one of the cells in the range changes?

Thank you

**HaHoBe** · 08-23-2012, 03:49 AM

Hi, BVZM,

WorksheetFunction Now() will be updated on any calculation the workbook/worksheet does. Maybe have a look at Stephen Bullen´s Homepage and download LastChng.zip which is a workbook showing one way of reaching the desired result using circular references.

BTW: I wouldn´t use a Function but use the Worksheet_Change-Event to put in Date and Time.

Ciao,
Holger

**BVZM** · 08-24-2012, 03:31 AM

Thank you Holger, will give that a try.

Regards
Beana

**HaHoBe** · 08-24-2012, 03:53 AM

Hi, Beana,

if the entries are made manually this code goes behind the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H7:H25")) Is Nothing Then
  Range("H4").Value = Now
End If
End Sub

Ciao,
Holger

**BVZM** · 08-24-2012, 07:41 AM

Thanks Holger,
I would really like if there is a simple way to accomplish this. Please see attached image for that I'm trying to do Example.jpg

I tried to copy and paste the code above in vba on 'this worksheet', saved and returned to Excel. Not sure if I did the correct thing, but nothing changed on the sheet. Like I said, I'm not familiar with macros, to be honest...at all.

Regards

**HaHoBe** · 08-24-2012, 01:23 PM

Hi, BVZM,

I would have preferred a workbook over a picture, especially since you claim not to be that good with macros/VBA. If I see it correctly this should affect columns H, I, K, and M indepently? Or is it only column H like in your starting post?

Ciao,
Holger

Don't forget to mark thread as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [ code ] Your code here [ /code ] (without the spaces inside the brackets)