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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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
|
Similar Threads
