Time and date each entry
 

Hi,

I am creating a spreadsheet to record and manage IT faults under the
following column headings.

TIME DATE Fault Description Reported By Reported
To Result

Is there any way to get Excel to generatenew time and date to reflect when
each Fault Description is entered on a new Row ?

Thanks

Time and date each entry
 

Right click on the sheet tab and select view code.

put in code like this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 3 Then
On Error GoTo errHandler
Application.EnableEvents = False
With Cells(Target.Row, 1)
.Value = Time
.NumberFormat = "hh:mm:ss"
End With
With Cells(Target.Row, 2)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
Columns("A:B").AutoFit
End If
errHandler:
Application.EnableEvents = True
End Sub

If you are not familiar with events, look at Chip Pearson's page
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"MAS" wrote in message
...
Hi,

I am creating a spreadsheet to record and manage IT faults under the
following column headings.

TIME DATE Fault Description Reported By
Reported
To Result

Is there any way to get Excel to generatenew time and date to reflect when
each Fault Description is entered on a new Row ?

Thanks

Time and date each entry
 

Tom

Thats it.. Cheers.

"Tom Ogilvy" wrote in message
...
Right click on the sheet tab and select view code.

put in code like this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 3 Then
On Error GoTo errHandler
Application.EnableEvents = False
With Cells(Target.Row, 1)
.Value = Time
.NumberFormat = "hh:mm:ss"
End With
With Cells(Target.Row, 2)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
Columns("A:B").AutoFit
End If
errHandler:
Application.EnableEvents = True
End Sub

If you are not familiar with events, look at Chip Pearson's page
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"MAS" wrote in message
...
Hi,

I am creating a spreadsheet to record and manage IT faults under the
following column headings.

TIME DATE Fault Description Reported By
Reported
To Result

Is there any way to get Excel to generatenew time and date to reflect
when
each Fault Description is entered on a new Row ?

Thanks

Time and date each entry
 

Well Almost...

I have now found that when I protect the worksheet the date writes correctly
but the time does not get written to its cell ?

Neither the Date or Time Cells are locked and it makes no difference if I
apply or unapply validation rules ?

Any ideas folks !


"MAS" wrote in message
...
Tom

Thats it.. Cheers.

"Tom Ogilvy" wrote in message
...
Right click on the sheet tab and select view code.

put in code like this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 3 Then
On Error GoTo errHandler
Application.EnableEvents = False
With Cells(Target.Row, 1)
.Value = Time
.NumberFormat = "hh:mm:ss"
End With
With Cells(Target.Row, 2)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
Columns("A:B").AutoFit
End If
errHandler:
Application.EnableEvents = True
End Sub

If you are not familiar with events, look at Chip Pearson's page
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"MAS" wrote in message
...
Hi,

I am creating a spreadsheet to record and manage IT faults under the
following column headings.

TIME DATE Fault Description Reported By
Reported
To Result

Is there any way to get Excel to generatenew time and date to reflect
when
each Fault Description is entered on a new Row ?

Thanks

Time and date each entry
 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 3 Then
On Error GoTo errHandler
Application.EnableEvents = False

' add line to unprotect
ActiveSheet.Unprotect

With Cells(Target.Row, 1)
.Value = Time
.NumberFormat = "hh:mm:ss"
End With
With Cells(Target.Row, 2)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
Columns("A:B").AutoFit
End If
errHandler:
Application.EnableEvents = True

' add line to protect
ActiveSheet.Protect
End Sub

if you have a password

Activesheet.Unprotect Password:="ABCD"


ActiveSheet.Protect Password:="ABCD"

--
Regards,
Tom Ogilvy


"MAS" wrote in message
...
Well Almost...

I have now found that when I protect the worksheet the date writes
correctly
but the time does not get written to its cell ?

Neither the Date or Time Cells are locked and it makes no difference if I
apply or unapply validation rules ?

Any ideas folks !


"MAS" wrote in message
...
Tom

Thats it.. Cheers.

"Tom Ogilvy" wrote in message
...
Right click on the sheet tab and select view code.

put in code like this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 3 Then
On Error GoTo errHandler
Application.EnableEvents = False
With Cells(Target.Row, 1)
.Value = Time
.NumberFormat = "hh:mm:ss"
End With
With Cells(Target.Row, 2)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
Columns("A:B").AutoFit
End If
errHandler:
Application.EnableEvents = True
End Sub

If you are not familiar with events, look at Chip Pearson's page
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"MAS" wrote in message
...
Hi,

I am creating a spreadsheet to record and manage IT faults under the
following column headings.

TIME DATE Fault Description Reported By
Reported
To Result

Is there any way to get Excel to generatenew time and date to reflect
when
each Fault Description is entered on a new Row ?

Thanks