Date stamp in worksheets

[Luc Poppe]

I would like to place a date stamp in a (any) cell each time a worksheet is
editted (not just merely clicked, but add/change text in a cell or trigger a
re-calcualtion). I have multiple worksheets and I want to add this in each
worksheet. It can not be a global workbook datestamp, rather independant for
every sheet in that workbook.
thanks

 

[The Code Cage Team]

This will date stamp the active sheet in A1 when a change is made:

Enter this in the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Range("A1").Value = Now()
End Sub

Change Now() for DATE if you do not want the time


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com

 

[Luc Poppe]

It works, but it seems to add an unacceptable calculation time, making
navigating and editing very "sticky". The size of the file is about 3 Mb and
has 18 worksheets with minimal formula calculations. It takes about 3 seconds
before the cursor is active again. And this is on a 2.5 GH quad core machine
(Dell T7400)

 

[Simon Lloyd]

Try this where calculation is turned off while the date stamp take
place;

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
With Application
..Calculation = xlManual
End With
Range("A1").Value = Now()
With Application
..Calculation = xlAutomatic
End With
End Sub


--
Simon Lloyd

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com)

 

[Luc Poppe]

Simon,
When copy/paste the lines you listed below,it does not work as the
formatting seems to be important (sorry, I'm a novice in VBA stuff), I tried
to format like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Application.Calculation = xlManual
End With
Range("D1").Value = Now()
With Application.Calculation = xlAutomatic
End With
End Sub

but didn't work, any hints?
BTW, due to width limits of this interface, the first line should read as 1
complete line.

Thanks,

 

[The Code Cage Team]

When you say it didn't work what do you mean, in which way did it not
work?

Feel free to join our forums where you can upload a test workbook which
we can help you with!


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com

 

[Luc Poppe]

I got a compile error in the VBA window (text turns red)

 

[Simon Lloyd]

Luc, you need to be a little more specific, which part turned red?,
where did you paste the code?


--
Simon Lloyd

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com)

 

[Luc Poppe]

Can I e-mail you directly to show some snapshots of the error code and
compile issues ? I can't paste snapshots or attach files in this interface.

Thanks

 

[Simon Lloyd]

Hey, just join our forums its free and you can upload workbooks...et

Look dorward to seeing you there

--
Simon Lloy

Regards
Simon Lloy
'www.thecodecage.com' (http://www.thecodecage.com

 

[lucpoppe]

Simon,

I uploaded snapshots of the error in excel file "Date stamp 10-31-08",
don't know where the file went, hopefully on your server somewhere. No
way to insert snapshots in this interface?


+-------------------------------------------------------------------+
|Filename: Date stamp issue 10-31-08.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=2 |
+-------------------------------------------------------------------+

 

[Simon Lloyd]

Yes you can insert pictures here, in your editor (when you make a post)
you should see a yellow square with what looks like a mountain range on
it this is for inserting images, or use the paperclip to attach any file
or image, you can find out more 'here'
(http://www.thecodecage.com/forumz/faq.php)


--
Simon Lloyd

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com)