Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time entry auto changes to date and then time ? | Excel Worksheet Functions | |||
Automate date & time entry | Excel Worksheet Functions | |||
Time and Date Entry Shortcuts? | Excel Worksheet Functions | |||
Data Entry date/time? | Excel Discussion (Misc queries) | |||
Date and time entry | Excel Programming |