| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mixed format
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mixed format
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mixed format
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mixed format
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mixed format
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mixed format
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mixed 3D Charts | Charts and Charting in Excel | |||
| format painter wont copy mixed font colors from one cell to next | Excel Discussion (Misc queries) | |||
| Mixed Cell | Excel Worksheet Functions | |||
| A mixed request | Excel Programming | |||
| A mixed bag of questions | Excel Programming | |||
Linear Mode
