Posted to microsoft.public.excel.misc
|
|
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.
|