automatically enter time

R

rvik

hi,

i have records in a column. the records are job works to be done. once
o job is done , i mark a "x" in the next column, indicating job
completed. i want the time of compeltion in the third column entered
automatically, once i make a mark in the second column. The mark need
not necessarily be a "x". it could be anything, say like a check box
also.

can it be done???

Thanks for your help..
 
R

Rob van Gelder

Rvik,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then Target.Offset(0, 1).Value = Now()
End Sub

Rob
 
B

Bob Phillips

It goes in the worksheet code module. Right-click on the sheet name tab,
select view Code from the menu, and copy the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

rvik

bob,

it worked, but in column c where the time is supposed to be displaced,
the time is dispalced as #########.

i tried formatting the entire column but in vain. i have to format
each cell individually to display the time
 
B

Bob Phillips

Just make tyhe column wider.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

rvik

bob,

i want only the time to be displace... but widening the column, th
date & time is displaye
 
B

Bob Phillips

Sorry, didn't read the OP>

Replace Rob's code with this modified version

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target.Offset(0, 1).Value = Now - Date
Target.Offset(0, 1).NumberFormat = "[h]:mm:ss"
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

rvik

bob,

pooohhhh.... at last i got ir right... thanks a lot bobby...


can u pls look into my other thread "copying data...."

anyway thanks a lot
 
R

rvik

i want to hide the code and also i would like to have the thrid colum
protected, so that others don't change the time.

If i share thebook, others must beb able to type in only in the secon
column. the thrid column displaying the time , should not be editable

thank
 
R

rvik

i would like to share this xl sheet on a network and let others also t
mark x in the sheet to record a time but this time should be standar
i.e. it should pick up the time from one central location..say th
server

any solution
 
Top