Concert number to time
I use the code below to force all text to be in upper case. Now I need to
ensure the the number entered in column Q is converted to a time format. The time is always entered in the 24 hour format (18:45 = 6:45 pm, 0645 = 6:45 am) but the often forget the " : ". These cells are not used for calculations, but they still need to be in the proper format. so is there any way to force 1845 to me converted to 18:45 or 645 to 06:45 while ignoring a correctly entered number? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub End If On Error GoTo ErrHandler: If Not Application.Intersect(Me.Range("G7:P2041"), Target) Is Nothing Then If IsNumeric(Target.Value) = False Then Application.EnableEvents = False Target.Value = StrConv(Target.Text, vbUpperCase) End If Application.EnableEvents = True End If ErrHandler: Application.EnableEvents = True End Sub |
Concert number to time
I've modified (and tightened up) your code in order to add the functionality
you asked for... see if this does what you want: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrHandler: If Target.Count = 1 And Not Application.Intersect( _ Me.Range("G7:P2041"), Target) Is Nothing Then Application.EnableEvents = False If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _ And Len(Target.Value) < 5 Then Target.Value = Format$(Target.Value, "'00\:00") Else Target.Value = UCase$(Target.Value) End If End If ErrHandler: Application.EnableEvents = True End Sub Rick "Patrick C. Simonds" wrote in message ... I use the code below to force all text to be in upper case. Now I need to ensure the the number entered in column Q is converted to a time format. The time is always entered in the 24 hour format (18:45 = 6:45 pm, 0645 = 6:45 am) but the often forget the " : ". These cells are not used for calculations, but they still need to be in the proper format. so is there any way to force 1845 to me converted to 18:45 or 645 to 06:45 while ignoring a correctly entered number? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub End If On Error GoTo ErrHandler: If Not Application.Intersect(Me.Range("G7:P2041"), Target) Is Nothing Then If IsNumeric(Target.Value) = False Then Application.EnableEvents = False Target.Value = StrConv(Target.Text, vbUpperCase) End If Application.EnableEvents = True End If ErrHandler: Application.EnableEvents = True End Sub |
All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com