Mixed format

P

Patrick C. Simonds

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
 
R

Rick Rothstein \(MVP - VB\)

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

Rick
 
R

Rick Rothstein \(MVP - VB\)

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
 
P

Patrick C. Simonds

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.
 
P

Patrick C. Simonds

Yes it does. Thank you.


Rick Rothstein (MVP - VB) said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top