Mixed format

  • Thread starter Thread starter Patrick C. Simonds
  • Start date Start date
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
 
Can you show us some samples of the data that could be in cell U23?

Rick
 
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
 
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.
 
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
Click to expand...
 

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

Similar Threads

Code comonr to all worksheets 4
Cell justifcation 1
Trying to run excel vba code when cell value changes from empty to entered value. 1
leading zero issue when converting to time format 1
Insert code 2
Copying Cell Conflict with Worksheet Change to UpperCase 4
2 Macros in VBA??? 3
hide multiple worksheets based on a formula value in one cell 1

Back
Top