ExcelBanter - Mixed format

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Mixed format (https://www.excelbanter.com/excel-programming/416023-mixed-format.html)

Cell U23 has both text and time in it. What I was hoping was that the code
below would change the text to all caps and the number to a time format.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:

Application.EnableEvents = False

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("U23"), Target) Is Nothing Then
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

Can you show us some samples of the data that could be in cell U23?

Rick

"Patrick C. Simonds" wrote in message
...
Cell U23 has both text and time in it. What I was hoping was that the code
below would change the text to all caps and the number to a time format.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:

Application.EnableEvents = False

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("U23"), Target) Is Nothing Then
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

Shift starts at 14:30 driving route T

"Rick Rothstein (MVP - VB)" wrote in
message ...
Can you show us some samples of the data that could be in cell U23?

Rick

"Patrick C. Simonds" wrote in message
...
Cell U23 has both text and time in it. What I was hoping was that the
code below would change the text to all caps and the number to a time
format.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:

Application.EnableEvents = False

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("U23"), Target) Is Nothing Then
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

Does this Change event procedure do what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim CellVal As String
Dim Words() As String
If Target.Address = "$U$23" Then
CellVal = UCase(Target.Value)
Words = Split(CellVal, " ")
For X = 0 To UBound(Words)
If Words(X) Like String(Len(Words(X)), "#") _
And Len(Words(X)) < 5 Then
Words(X) = Format(Words(X), "0:00")
End If
Next
CellVal = Join(Words, " ")
On Error GoTo Err_Handler
Application.EnableEvents = False
Target.Value = CellVal
End If
Err_Handler:
Application.EnableEvents = True
End Sub

Rick

"Patrick C. Simonds" wrote in message
...
Shift starts at 14:30 driving route T

"Rick Rothstein (MVP - VB)" wrote in
message ...
Can you show us some samples of the data that could be in cell U23?

Rick

"Patrick C. Simonds" wrote in message
...
Cell U23 has both text and time in it. What I was hoping was that the
code below would change the text to all caps and the number to a time
format.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:

Application.EnableEvents = False

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("U23"), Target) Is Nothing Then
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

Unfortunately I can not always count on people to always use upper case
characters and use the time format when entering the time, and that is what
is required.

"Patrick C. Simonds" wrote in message
...
Shift starts at 14:30 driving route T

"Rick Rothstein (MVP - VB)" wrote in
message ...
Can you show us some samples of the data that could be in cell U23?

Rick

"Patrick C. Simonds" wrote in message
...
Cell U23 has both text and time in it. What I was hoping was that the
code below would change the text to all caps and the number to a time
format.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:

Application.EnableEvents = False

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("U23"), Target) Is Nothing Then
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

Yes it does. Thank you.

"Rick Rothstein (MVP - VB)" wrote in
message ...
Does this Change event procedure do what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim CellVal As String
Dim Words() As String
If Target.Address = "$U$23" Then
CellVal = UCase(Target.Value)
Words = Split(CellVal, " ")
For X = 0 To UBound(Words)
If Words(X) Like String(Len(Words(X)), "#") _
And Len(Words(X)) < 5 Then
Words(X) = Format(Words(X), "0:00")
End If
Next
CellVal = Join(Words, " ")
On Error GoTo Err_Handler
Application.EnableEvents = False
Target.Value = CellVal
End If
Err_Handler:
Application.EnableEvents = True
End Sub

Rick

"Patrick C. Simonds" wrote in message
...
Shift starts at 14:30 driving route T

"Rick Rothstein (MVP - VB)" wrote
in message ...
Can you show us some samples of the data that could be in cell U23?

Rick

"Patrick C. Simonds" wrote in message
...
Cell U23 has both text and time in it. What I was hoping was that the
code below would change the text to all caps and the number to a time
format.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:

Application.EnableEvents = False

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("U23"), Target) Is Nothing Then
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