ExcelBanter - auto-insert colon when entering times

Need VBA event code to auto-insert.

Copy/paste this code into Thisworkbook module and it will run on all sheets
when entering a time in column A

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
'enter 1245p and get 12:45:00PM
If Target.Column = 1 Then
On Error GoTo endit
Application.EnableEvents = False
If Len(Target) = 4 Then
hr = Left(Target, 1)
mn = Mid(Target, 2, 2)
ap = Right(Target, 1)
Else
hr = Left(Target, 2)
mn = Mid(Target, 3, 2)
ap = Right(Target, 1)
End If
Target.Value = TimeValue(hr & ":" & mn & " " & ap)
NumberFormat = "h:mm AM/PM"
endit:
Application.EnableEvents = True
End If
End Sub

Gord Dibben MS Excel MVP

On Tue, 1 Sep 2009 14:10:04 -0700, sami
wrote:

Would like to have the colon auto-insert on multiple worksheets. Hopefully
without having to write a macro.