Date stamp in worksheets
 

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

Date stamp in worksheets
 

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
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440

Date stamp in worksheets
 

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)


"The Code Cage Team" wrote:


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
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440

Date stamp in worksheets
 

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440

Date stamp in worksheets
 

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,


"Simon Lloyd" wrote:


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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440

Date stamp in worksheets
 

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
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440

Date stamp in worksheets
 

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

"The Code Cage Team" wrote:


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
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440

Date stamp in worksheets
 

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440

Date stamp in worksheets
 

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" wrote:


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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440

Date stamp in worksheets
 

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

Look dorward to seeing you there!


--
Simon Lloyd

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440

Date stamp in worksheets
 

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/attachmen...attachmentid=2 |
+-------------------------------------------------------------------+

--
lucpoppe
------------------------------------------------------------------------
lucpoppe's Profile: http://www.thecodecage.com/forumz/member.php?userid=12
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440

Date stamp in worksheets
 

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23440