Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date-time stamp
Hello,
My name is Daniel, Can you please help me with some code to do the following in Excel: Have one spreadsheet (with some protection I think); whenever someone alters the content of some cells, a DateTime stamp should be stored in another cell (protected). Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date-time stamp
For any changes done to unprotected area Range("A1:A20") a date/time stamp is
set in Column C which is the protected area. Right click the sheet tabView Code and paste the below code to the code module.. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Count = 1 Then Me.Unprotect Password:="dist" Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@") Me.Protect Password:="dist" End If End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Daniel" wrote: Hello, My name is Daniel, Can you please help me with some code to do the following in Excel: Have one spreadsheet (with some protection I think); whenever someone alters the content of some cells, a DateTime stamp should be stored in another cell (protected). Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date-time stamp
Daniel,
This assumes the cells where you enter the data are not protected and in this case it's A1 - A100. Right click your sheet tab, view code and paste the code below in. Enter data entered in a1 - A100 and you get the timestamp in the corresponding B1 - B100 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A100")) Is Nothing Then On Error Resume Next Application.EnableEvents = False ActiveSheet.Unprotect Password:="MyPass" Target.Offset(, 1) = Now ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True On Error GoTo 0 End If End Sub Mike "Daniel" wrote: Hello, My name is Daniel, Can you please help me with some code to do the following in Excel: Have one spreadsheet (with some protection I think); whenever someone alters the content of some cells, a DateTime stamp should be stored in another cell (protected). Thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date-time stamp
Jacob, Mike : Thank you very much for your kind help
Daniel "Mike H" wrote: Daniel, This assumes the cells where you enter the data are not protected and in this case it's A1 - A100. Right click your sheet tab, view code and paste the code below in. Enter data entered in a1 - A100 and you get the timestamp in the corresponding B1 - B100 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A100")) Is Nothing Then On Error Resume Next Application.EnableEvents = False ActiveSheet.Unprotect Password:="MyPass" Target.Offset(, 1) = Now ActiveSheet.Protect Password:="MyPass" Application.EnableEvents = True On Error GoTo 0 End If End Sub Mike "Daniel" wrote: Hello, My name is Daniel, Can you please help me with some code to do the following in Excel: Have one spreadsheet (with some protection I think); whenever someone alters the content of some cells, a DateTime stamp should be stored in another cell (protected). Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time Stamp for printing | Excel Worksheet Functions | |||
Date Time Stamp Dilemna | Excel Discussion (Misc queries) | |||
Date-Time Stamp | Excel Discussion (Misc queries) | |||
date/time stamp | Excel Worksheet Functions | |||
Date/Time stamp with one stroke? | Excel Discussion (Misc queries) |