Home |
Search |
Today's Posts |
#1
|
|||
|
|||
date/time stamp
I know I probably did post my reply correctly to get a possible solution to
my problem. Any further help would be greatly appreciated. Sorry, but I failed to mention the process correctly. If end user enters data in any cell in column A beginning at cell a2, then I would like the corresponding cell(same row) in column B to have a date/time stamp that would not change when the date/time changes or when the file is saved and reopened. Example: if user enters data in cell A2, then B2 would automatically display date/time stamp. If user enters data in cell A3, then B3 would have a date/time stamp...and so on..and so on. TIA. ----------------------------------------------- Enter this code in the module of the concerned sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Range("B1") = Now() End If End Sub Will change the date only when you change or enter value in A1 Mangesh ________________________________________ I'm using Excel 2003. Is this possible? If end user enters data in A1, can I add a date/time function that will automatically be entered in cell B1, but not update when the date/time changes or when the file is closed/opened? Using Today() or Now(), the date/time in B1 updates when file is closed and then opened. TIA |
#2
|
|||
|
|||
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then With Target .Offset(0, 1).Value = Time .Offset(0, 1).NumberFormat = "hh:mm:ss" End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips "Jan" wrote in message ... I know I probably did post my reply correctly to get a possible solution to my problem. Any further help would be greatly appreciated. Sorry, but I failed to mention the process correctly. If end user enters data in any cell in column A beginning at cell a2, then I would like the corresponding cell(same row) in column B to have a date/time stamp that would not change when the date/time changes or when the file is saved and reopened. Example: if user enters data in cell A2, then B2 would automatically display date/time stamp. If user enters data in cell A3, then B3 would have a date/time stamp...and so on..and so on. TIA. ----------------------------------------------- Enter this code in the module of the concerned sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Range("B1") = Now() End If End Sub Will change the date only when you change or enter value in A1 Mangesh ________________________________________ I'm using Excel 2003. Is this possible? If end user enters data in A1, can I add a date/time function that will automatically be entered in cell B1, but not update when the date/time changes or when the file is closed/opened? Using Today() or Now(), the date/time in B1 updates when file is closed and then opened. TIA |
#3
|
|||
|
|||
Bob,
I tried to adapt the number format so that date and time display because I need to have both. I have replaced your " "hh:mm:ss" with ""mm/dd/yy h:mm AM/PM;@". However, no matter what format I try to use the date displays as 01/0/1900. Do you have any suggestions to the the date display as the current date? "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then With Target .Offset(0, 1).Value = Time .Offset(0, 1).NumberFormat = "hh:mm:ss" End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips "Jan" wrote in message ... I know I probably did post my reply correctly to get a possible solution to my problem. Any further help would be greatly appreciated. Sorry, but I failed to mention the process correctly. If end user enters data in any cell in column A beginning at cell a2, then I would like the corresponding cell(same row) in column B to have a date/time stamp that would not change when the date/time changes or when the file is saved and reopened. Example: if user enters data in cell A2, then B2 would automatically display date/time stamp. If user enters data in cell A3, then B3 would have a date/time stamp...and so on..and so on. TIA. ----------------------------------------------- Enter this code in the module of the concerned sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Range("B1") = Now() End If End Sub Will change the date only when you change or enter value in A1 Mangesh ________________________________________ I'm using Excel 2003. Is this possible? If end user enters data in A1, can I add a date/time function that will automatically be entered in cell B1, but not update when the date/time changes or when the file is closed/opened? Using Today() or Now(), the date/time in B1 updates when file is closed and then opened. TIA |
#4
|
|||
|
|||
First, note that the macro as written will give undesired results if a
multiple selection includes more than one column, including column A. For instance, if A1:J10 is selected with C5 active, and C5 is changed by the user, all the cells in B1:K10 will be overwritten with the date stamp. One way to handle that would be to abort if there's a multiple selection. Second, Time only returns the time. For dates as well, use Now instead. I might amend the macro like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim rSelect As Range With Target If .Count 1 Then Exit Sub If Not Intersect(.Cells, Me.Range("A:A")) Is Nothing Then Application.EnableEvents = False With .Offset(0, 1) .Value = Now .NumberFormat = "dd/mm/yy hh:mm AM/PM" End With End If End With ws_exit: Application.EnableEvents = True End Sub In article , Jan wrote: I tried to adapt the number format so that date and time display because I need to have both. I have replaced your " "hh:mm:ss" with ""mm/dd/yy h:mm AM/PM;@". However, no matter what format I try to use the date displays as 01/0/1900. Do you have any suggestions to the the date display as the current date? |
#5
|
|||
|
|||
Oops. Delete the Dim rSelect As Range line (it was from a different
worksheet_change procedure that I use). In article , JE McGimpsey wrote: I might amend the macro like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim rSelect As Range |
#6
|
|||
|
|||
Is there a way to make it so that its "if the value of the cell changes" not "if its double clicked"?? Also, I would like to protect and hide those cells in the worksheet and not allow the users to edit or even see those date/times. Problem is when I protect the cells and they enter the data in cell a1 or whatever, it doesnt change the date/time cause well.... they're not allowed to change it so it doesn't do the change. -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=380369 |
#7
|
|||
|
|||
Take a look he
http://www.mcgimpsey.com/excel/timestamp.html In article , DKY wrote: Is there a way to make it so that its "if the value of the cell changes" not "if its double clicked"?? Also, I would like to protect and hide those cells in the worksheet and not allow the users to edit or even see those date/times. Problem is when I protect the cells and they enter the data in cell a1 or whatever, it doesnt change the date/time cause well.... they're not allowed to change it so it doesn't do the change. |
#8
|
|||
|
|||
Okay, so here's my code then (that I stole from this site and revised to fit my needs). Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Then Exit Sub If Not Intersect(.Cells, Me.Range("E:E")) Is Nothing Then Application.EnableEvents = False With .Offset(0, 2) .Value = Now .NumberFormat = "mmm dd yyyy hh:mm:ss AM/PM" End With End If End With ws_exit: Application.EnableEvents = True End Sub -------------------- How would I go about adding the windows log-on username to this? I found this http://blogs.officezealot.com/charle...2/10/3574.aspx Which says you can use this Code: -------------------- Function UserNameWindows() As String UserName = Environ("USERNAME") End Function -------------------- But where do I put the function in the code and how do I call it to use? -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=380369 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date/Time stamp with one stroke? | Excel Discussion (Misc queries) | |||
date/time | Excel Worksheet Functions | |||
Date/time range based calculations | Excel Discussion (Misc queries) | |||
bringing data from one workbook to another using a date stamp func | Excel Worksheet Functions | |||
How do i convert a number of seconds to a date/time? | Excel Worksheet Functions |