ohsoquiet
07-12-2010, 04:06 PM
Until today I had never heard of VBA code so I apologize in advance for asking what is probably an obvious question. I've been asked to find a way to enter times into Excel that do not require the typist to enter a colon or using military time. For instance, one could type "443 p" and Excel would turn that into "4:43 PM" or I could enter "1231 a" and Excel would turn it into 12:31 AM. I have found a code that does this, but despite hours of searching I can not figure out how to make the code work on more than one column. Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range, dblTime As Double
On Error GoTo ExitPoint
If Intersect(Target, Columns(4)) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rngCell In Intersect(Target, Columns(4)).Cells
If rngCell.Value <> "" Then
With rngCell
dblTime = TimeValue(Format(Val(.Value), "00\:00"))
dblTime = TimeSerial(Hour(dblTime) Mod 12, Minute(dblTime), 0)
dblTime = dblTime + IIf(UCase(Right(rngCell.Value, 1)) = "P", 0.5, 0)
.NumberFormat = "h:mm AM/PM"
.Value = dblTime
End With
End If
Next rngCell
ExitPoint:
Application.EnableEvents = True
End Sub
I've figured out that the number "4" after "Target, Columns" refers to Column D. So I can obviously change that to "3" to make it refer to Column C, but what if I want it to refer to both columns C and D? Or Column A and F? Or A, C, and F? Any help would be greatly appreciated!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range, dblTime As Double
On Error GoTo ExitPoint
If Intersect(Target, Columns(4)) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rngCell In Intersect(Target, Columns(4)).Cells
If rngCell.Value <> "" Then
With rngCell
dblTime = TimeValue(Format(Val(.Value), "00\:00"))
dblTime = TimeSerial(Hour(dblTime) Mod 12, Minute(dblTime), 0)
dblTime = dblTime + IIf(UCase(Right(rngCell.Value, 1)) = "P", 0.5, 0)
.NumberFormat = "h:mm AM/PM"
.Value = dblTime
End With
End If
Next rngCell
ExitPoint:
Application.EnableEvents = True
End Sub
I've figured out that the number "4" after "Target, Columns" refers to Column D. So I can obviously change that to "3" to make it refer to Column C, but what if I want it to refer to both columns C and D? Or Column A and F? Or A, C, and F? Any help would be greatly appreciated!