 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / August 2008Automatic Time and Date
Hi, I have a situation similar to D. West where I'd like for the current time and date to be entered in col. A whenever a new row is populated with values. I tried the setting col. A to: =IF(COUNTA(B3:IV3)<>0,NOW(),"") like Gary Brown suggested for D.West. That sort of worked, but I'd like for that value to remain, not be subject to recalculation whenever something new is added to that or other rows. Any way to specify that value to remain unchanged once it's calculated the first time? Thanks. Larry you might try to use Worksheet_Change event (go to sheet tab, right- click on it, click Show code, instead of "(General)" select "Worksheet" and then "Change" in the rightmost window) Private Sub Worksheet_Change(ByVal Target As Range) Cells(Selection.Row - 1, 1) = Time End Sub provided entering data moves you 1 row down however this will cause time to be entered to cells in 1st column also on editing any cell HIH Hi Jarek. I couldn't get it to work. I may have the Cell callout messed up. Can you show me an example of what to put in for "Cells(Selection.Row - 1, 1) = Time". Thanks. Larry > you might try to use Worksheet_Change event (go to sheet tab, right- > click on it, click Show code, instead of "(General)" select [quoted text clipped - 9 lines] > > HIH ...Gord's solution is very good... Private Sub WorkSheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then With Target If .Value <> "" Then .Offset(0, -1).Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub Will add a timestamp to cell in column A when a change is made in corresponding cell in row of column B The timestamp does not update unless column A is edited. This is sheet event code. Right-click on the sheet tab and "Viedw Code" Copy/paste into that sheet module. Alt + q to return to the Excel window. Gord Dibben MS Excel MVP >Hi, I have a situation similar to D. West where I'd like for the current >time and date to be entered in col. A whenever a new row is populated with [quoted text clipped - 4 lines] >unchanged once it's calculated the first time? Thanks. >Larry Gord, thanks. Your suggestion seems to work great. Now, I don't really want the date stamp in column A. How do I change the date stamp to, say, column E? Thanks. Larry > Private Sub WorkSheet_Change(ByVal Target As Range) > On Error GoTo ws_exit: [quoted text clipped - 33 lines] >>unchanged once it's calculated the first time? Thanks. >>Larry Column E Offset from column B would be .Offset(0, 3) instead of .Offset(0, -1) Offset(Row, Column) is the syntax. Gord >Gord, thanks. Your suggestion seems to work great. Now, I don't really want >the date stamp in column A. How do I change the date stamp to, say, column [quoted text clipped - 39 lines] >>>unchanged once it's calculated the first time? Thanks. >>>Larry This worked!!! You guys are lifesavers! Thanks so much. I do have one other question. Is there any way to change it to regular time instead of military time? > Column E Offset from column B would be > [quoted text clipped - 47 lines] > >>>unchanged once it's calculated the first time? Thanks. > >>>Larry re-format yr cells HIH I am currently doing something similar, where data inputted in column A will insert the date into column E. This is to copied through the whole worksheet (well down to about 500ish rows) I have used the formula: =IF(COUNTA(A102)<>0,NOW(),"") In column E and inserted the following in visual basics: Private Sub WorkSheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then With Target If .Value <> "" Then .Offset(0, -1).Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With End If ws_exit: Application.EnableEvents = True End Sub However, redaing the below, it is likely that the above is incorrect (offset values error??) Please could someone help and advise, NB, this will be used on machines that use XP office, 2003 & 2007. Thanks Aaron > This worked!!! You guys are lifesavers! Thanks so much. I do have one > other [quoted text clipped - 60 lines] >> >>>unchanged once it's calculated the first time? Thanks. >> >>>Larry why not If Not Intersect(Target, Me.Range("E:E")) Is Nothing Then ? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.