|
Time Stamp
Any macro to add time of recording in cells of column A into corressponding
cells of column B, whether the entry is punched in or multiple ones are pasted? Having the time recorded so to be values so as to restrict from being changed. |
Time Stamp
Faraz
When you enter data in Column A either by direct entry or pasting, Column B will get a time stamp if none is currently there. If B has a time stamp it will not change. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub An alternative to actually lock column B before and after the time stamp is entered. Unlock all columns except for Column B then protect the sheet with the password "justme"(no quotes) Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then ActiveSheet.Unprotect Password:="justme" n = Target.Row If Target.Value < "" _ And Target.Offset(0, 1).Value = "" Then With Target.Offset(0, 1) .Value = Now .Locked = True End With End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP On Sat, 6 Jan 2007 12:56:00 -0800, FARAZ QURESHI wrote: Any macro to add time of recording in cells of column A into corressponding cells of column B, whether the entry is punched in or multiple ones are pasted? Having the time recorded so to be values so as to restrict from being changed. |
Time Stamp
Thanx 4 your help Gord, but the code doesn't seem to b working when I paste
an array or range suppose upon cells A1:A10 to reflect timestamp upon B1:B10 "Gord Dibben" wrote: Faraz When you enter data in Column A either by direct entry or pasting, Column B will get a time stamp if none is currently there. If B has a time stamp it will not change. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub An alternative to actually lock column B before and after the time stamp is entered. Unlock all columns except for Column B then protect the sheet with the password "justme"(no quotes) Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then ActiveSheet.Unprotect Password:="justme" n = Target.Row If Target.Value < "" _ And Target.Offset(0, 1).Value = "" Then With Target.Offset(0, 1) .Value = Now .Locked = True End With End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP On Sat, 6 Jan 2007 12:56:00 -0800, FARAZ QURESHI wrote: Any macro to add time of recording in cells of column A into corressponding cells of column B, whether the entry is punched in or multiple ones are pasted? Having the time recorded so to be values so as to restrict from being changed. |
Time Stamp
Apologies.
I re-read and now note you did state "multiple ones are pasted". You and I definitely need outside help for this. Gord On Sun, 7 Jan 2007 01:47:00 -0800, FARAZ QURESHI wrote: Thanx 4 your help Gord, but the code doesn't seem to b working when I paste an array or range suppose upon cells A1:A10 to reflect timestamp upon B1:B10 "Gord Dibben" wrote: Faraz When you enter data in Column A either by direct entry or pasting, Column B will get a time stamp if none is currently there. If B has a time stamp it will not change. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub An alternative to actually lock column B before and after the time stamp is entered. Unlock all columns except for Column B then protect the sheet with the password "justme"(no quotes) Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then ActiveSheet.Unprotect Password:="justme" n = Target.Row If Target.Value < "" _ And Target.Offset(0, 1).Value = "" Then With Target.Offset(0, 1) .Value = Now .Locked = True End With End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP On Sat, 6 Jan 2007 12:56:00 -0800, FARAZ QURESHI wrote: Any macro to add time of recording in cells of column A into corressponding cells of column B, whether the entry is punched in or multiple ones are pasted? Having the time recorded so to be values so as to restrict from being changed. |
Time Stamp
Thanks Faraz, for your patience.
Keep eye on this thread. Someone(that includes just about anyone) with greater VBA skills will jump in and bail me out.........please<g Gord On Sun, 7 Jan 2007 11:50:01 -0800, FARAZ QURESHI wrote: Hi Its all right Gord, u did your best. Sure do oblige all your assistance however do look out for the solution. Shall further oblige if u email me the result when found, at / . Thanx again. Regards FARAZ "Gord Dibben" wrote: Apologies. I re-read and now note you did state "multiple ones are pasted". You and I definitely need outside help for this. Gord On Sun, 7 Jan 2007 01:47:00 -0800, FARAZ QURESHI wrote: Thanx 4 your help Gord, but the code doesn't seem to b working when I paste an array or range suppose upon cells A1:A10 to reflect timestamp upon B1:B10 "Gord Dibben" wrote: Faraz When you enter data in Column A either by direct entry or pasting, Column B will get a time stamp if none is currently there. If B has a time stamp it will not change. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub An alternative to actually lock column B before and after the time stamp is entered. Unlock all columns except for Column B then protect the sheet with the password "justme"(no quotes) Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then ActiveSheet.Unprotect Password:="justme" n = Target.Row If Target.Value < "" _ And Target.Offset(0, 1).Value = "" Then With Target.Offset(0, 1) .Value = Now .Locked = True End With End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP On Sat, 6 Jan 2007 12:56:00 -0800, FARAZ QURESHI wrote: Any macro to add time of recording in cells of column A into corressponding cells of column B, whether the entry is punched in or multiple ones are pasted? Having the time recorded so to be values so as to restrict from being changed. |
Time Stamp
Faraz
Revised code. Hope it fits your needs. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myrange On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then myrange = Target.Value Target.Offset(0, 1).Value = Now End If enditall: Application.EnableEvents = True End Sub Gord On Sun, 07 Jan 2007 13:35:14 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Thanks Faraz, for your patience. Keep eye on this thread. Someone(that includes just about anyone) with greater VBA skills will jump in and bail me out.........please<g Gord On Sun, 7 Jan 2007 11:50:01 -0800, FARAZ QURESHI wrote: Hi Its all right Gord, u did your best. Sure do oblige all your assistance however do look out for the solution. Shall further oblige if u email me the result when found, at / . Thanx again. Regards FARAZ "Gord Dibben" wrote: Apologies. I re-read and now note you did state "multiple ones are pasted". You and I definitely need outside help for this. Gord On Sun, 7 Jan 2007 01:47:00 -0800, FARAZ QURESHI wrote: Thanx 4 your help Gord, but the code doesn't seem to b working when I paste an array or range suppose upon cells A1:A10 to reflect timestamp upon B1:B10 "Gord Dibben" wrote: Faraz When you enter data in Column A either by direct entry or pasting, Column B will get a time stamp if none is currently there. If B has a time stamp it will not change. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub An alternative to actually lock column B before and after the time stamp is entered. Unlock all columns except for Column B then protect the sheet with the password "justme"(no quotes) Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then ActiveSheet.Unprotect Password:="justme" n = Target.Row If Target.Value < "" _ And Target.Offset(0, 1).Value = "" Then With Target.Offset(0, 1) .Value = Now .Locked = True End With End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP On Sat, 6 Jan 2007 12:56:00 -0800, FARAZ QURESHI wrote: Any macro to add time of recording in cells of column A into corressponding cells of column B, whether the entry is punched in or multiple ones are pasted? Having the time recorded so to be values so as to restrict from being changed. |
Time Stamp
Unbelieveable cool Gord..........a keeper for sure......so simple once it's
done <g Vaya con Dios, Chuck, CABGx3 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Faraz Revised code. Hope it fits your needs. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myrange On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then myrange = Target.Value Target.Offset(0, 1).Value = Now End If enditall: Application.EnableEvents = True End Sub Gord On Sun, 07 Jan 2007 13:35:14 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Thanks Faraz, for your patience. Keep eye on this thread. Someone(that includes just about anyone) with greater VBA skills will jump in and bail me out.........please<g Gord On Sun, 7 Jan 2007 11:50:01 -0800, FARAZ QURESHI wrote: Hi Its all right Gord, u did your best. Sure do oblige all your assistance however do look out for the solution. Shall further oblige if u email me the result when found, at / . Thanx again. Regards FARAZ "Gord Dibben" wrote: Apologies. I re-read and now note you did state "multiple ones are pasted". You and I definitely need outside help for this. Gord On Sun, 7 Jan 2007 01:47:00 -0800, FARAZ QURESHI wrote: Thanx 4 your help Gord, but the code doesn't seem to b working when I paste an array or range suppose upon cells A1:A10 to reflect timestamp upon B1:B10 "Gord Dibben" wrote: Faraz When you enter data in Column A either by direct entry or pasting, Column B will get a time stamp if none is currently there. If B has a time stamp it will not change. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub An alternative to actually lock column B before and after the time stamp is entered. Unlock all columns except for Column B then protect the sheet with the password "justme"(no quotes) Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then ActiveSheet.Unprotect Password:="justme" n = Target.Row If Target.Value < "" _ And Target.Offset(0, 1).Value = "" Then With Target.Offset(0, 1) .Value = Now .Locked = True End With End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP On Sat, 6 Jan 2007 12:56:00 -0800, FARAZ QURESHI wrote: Any macro to add time of recording in cells of column A into corressponding cells of column B, whether the entry is punched in or multiple ones are pasted? Having the time recorded so to be values so as to restrict from being changed. |
Time Stamp
Thanks Chuck
Further revision to keep time stamp from changing if cells copied-to a second time. I think that's what Faraz originally wanted. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myrange On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then myrange = Target.Value For Each myrange In Target If myrange.Offset(0, 1).Value = "" Then myrange.Offset(0, 1).Value = Format(Now, "h:mm:ss") End If Next End If enditall: Application.EnableEvents = True End Sub Just don't try to copy and paste an entire column Gord.........who is done with project. On Mon, 8 Jan 2007 17:43:36 -0500, "CLR" wrote: Unbelieveable cool Gord..........a keeper for sure......so simple once it's done <g Vaya con Dios, Chuck, CABGx3 "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Faraz Revised code. Hope it fits your needs. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myrange On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then myrange = Target.Value Target.Offset(0, 1).Value = Now End If enditall: Application.EnableEvents = True End Sub Gord On Sun, 07 Jan 2007 13:35:14 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Thanks Faraz, for your patience. Keep eye on this thread. Someone(that includes just about anyone) with greater VBA skills will jump in and bail me out.........please<g Gord On Sun, 7 Jan 2007 11:50:01 -0800, FARAZ QURESHI wrote: Hi Its all right Gord, u did your best. Sure do oblige all your assistance however do look out for the solution. Shall further oblige if u email me the result when found, at / . Thanx again. Regards FARAZ "Gord Dibben" wrote: Apologies. I re-read and now note you did state "multiple ones are pasted". You and I definitely need outside help for this. Gord On Sun, 7 Jan 2007 01:47:00 -0800, FARAZ QURESHI wrote: Thanx 4 your help Gord, but the code doesn't seem to b working when I paste an array or range suppose upon cells A1:A10 to reflect timestamp upon B1:B10 "Gord Dibben" wrote: Faraz When you enter data in Column A either by direct entry or pasting, Column B will get a time stamp if none is currently there. If B has a time stamp it will not change. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub An alternative to actually lock column B before and after the time stamp is entered. Unlock all columns except for Column B then protect the sheet with the password "justme"(no quotes) Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then ActiveSheet.Unprotect Password:="justme" n = Target.Row If Target.Value < "" _ And Target.Offset(0, 1).Value = "" Then With Target.Offset(0, 1) .Value = Now .Locked = True End With End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP On Sat, 6 Jan 2007 12:56:00 -0800, FARAZ QURESHI wrote: Any macro to add time of recording in cells of column A into corressponding cells of column B, whether the entry is punched in or multiple ones are pasted? Having the time recorded so to be values so as to restrict from being changed. |
| All times are GMT +1. The time now is 10:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com