entering current time in shared work book - 2003

  • Thread starter Thread starter Radhakant Panigrahi
  • Start date Start date
R

Radhakant Panigrahi

Hi,
i am maintaing a excel tracker in shared drive where i am manually entering
the time when a person came into the office. Is it possible that when a
person comes he himself open the shared file and click the cell corresponding
to his name so that the time of that moment can be stored and that can not be
further updated with new value (time)...

below is the format
Name of Person
Date Name A Name B Name C
21-Apr-10 6:44 PM 6:48 PM 6:40 PM
22-Apr-10 6:30 PM 6:34 PM 6:31 PM
23-Apr-10 6:35 PM 6:39 PM 6:40 PM
 
I would try first to setup a list on another sheet for all the people and on
the main sheet have a dropdown to refer to the list. Then in the sheet code,
setup an event VB like the following which if someone selects from the
dropdown in column A then it will go over 1 and put a date/time


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A1:A2000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "mm/dd/yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
 
Back
Top