Time Stamp

[Guest]

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.

 

[Gord Dibben]

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

 

[Guest]

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]

Apologies.

I re-read and now note you did state "multiple ones are pasted".

You and I definitely need outside help for this.


Gord

 

[Guest]

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 (e-mail address removed) / (e-mail address removed).

Thanx again.

Regards

FARAZ

 

[Gord Dibben]

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

 

[Gord Dibben]

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

 

[CLR]

Unbelieveable cool Gord..........a keeper for sure......so simple once it's
done <g>

Vaya con Dios,
Chuck, CABGx3

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

 

[Gord Dibben]

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.

Unbelieveable cool Gord..........a keeper for sure......so simple once it's
done <g>

Vaya con Dios,
Chuck, CABGx3