Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating date from a Date & Time stamp | Excel Discussion (Misc queries) | |||
Time & Date Stamp 2 worksheets | Excel Discussion (Misc queries) | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
Date stamp spreadsheet in excel to remind me of completion date | Excel Worksheet Functions | |||
Date Stamp | Excel Discussion (Misc queries) |