Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
auto-insert colon when entering times
Would like to have the colon auto-insert on multiple worksheets. Hopefully
without having to write a macro. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I insert a colon into a column of existing numbers | Excel Discussion (Misc queries) | |||
Auto insert row while entering raw data | Excel Discussion (Misc queries) | |||
Add colon to times | New Users to Excel | |||
Is there a way to enter times without typing a colon? | Excel Discussion (Misc queries) | |||
Insert colon in text in cell | Excel Discussion (Misc queries) |