Update date in one cell when any cell in a range is modified

[Lars]

Hi group,

I have one cell in a worksheet where I would like to have the date
refreshed *if and when* any of the cells in several columns and abt 25
rows are modified.

How can I do this?

My date goes in H1.

The cells which may be modified are C4:C15, D415, G4:15, K4:K33 and
L4:L33


Lars
Stockholm

 

[Dave Peterson]

First, G4:15 is really G4:G15????

Are they updated by typing?

If yes, you can tie into the worksheet_Change event.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window that just opened:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToInspect As Range

Set myRngToInspect = Me.Range("C4:C15,D415,G4:G15,K4:K33,L4:L33")

If Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Me.Range("h1").Value = Date
Application.EnableEvents = True
End Sub

Be aware that anything that excel sees as a change (even hitting F2, then enter)
will change the date in H1.

And you may notice that the Undo/Redo stack is sometimes killed, too. As well
as the clipboard.

If you really want the date and time, change this:
Me.Range("h1").Value = Date
to
Me.Range("h1").Value = Now

 

[Lars]

First, G4:15 is really G4:G15????

Yes it is.

Are they updated by typing?
Yes

If yes, you can tie into the worksheet_Change event.

Terrific! Thank you.
................

Actually I have this Excel sheet as a Google doc. Those handle
functions fine but does not seem to allow Vba-code.

By the way, can I specify the date format?

Me.Range("h1").Value = Date

I tried to format the cell itself in the worksheet as DDD DD/MM but it
would not take it.


Lars
Stockholm

 

[Dave Peterson]

I guess you're out of luck if you want to use the workbook in google docs--I
don't use it enough to know any details about i.

Manually formatting that cell should have worked, but you can also do it in
code:

with Me.Range("h1")
.numberformat = "ddd dd/mm"
.Value = Date
end with

 

[hall.jeff]

You're indeed out of luck. Google Docs don't let you into the code...
If you're using google docs for the convenience factor then this is a
problem... if you're using google docs because it's free, I would
suggest open office (which doesn't support vba but does support python
and a couple other languages)

 

[Lars]

You're indeed out of luck. Google Docs don't let you into the code...
If you're using google docs for the convenience factor then this is a
problem... if you're using google docs because it's free,

Too bad. I already have Office 2003 so the cost is not an issue.

This is a long list of people that I just tick in a box when they are
present, and then my boss can check on it. Saves me mailing a whole
workbook every day.

Guess I just have to remember updating the date field manually.

Thanks for your input guys!


Lars
Stockholm