time format
Here is the code I posted elsewhere in this thread modified to handle the
range you asked about...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
Application.EnableEvents = False
With Target
If Not Intersect(Target, Range("D8:G14")) Is Nothing Then
If InStr(.Value, ":") = 0 Then
.Value = Replace(.Value, "a", ":00 AM", , , vbTextCompare)
.Value = Replace(.Value, "p", ":00 AM", , , vbTextCompare)
End If
End If
End With
Application.EnableEvents = True
End Sub
Remember, this code allows you to enter your time either in the format you
originally asked about or in real Excel time format.
--
Rick (MVP - Excel)
"art" wrote in message
...
Is it possible to make this vba just for certain cells, like from D8
thrug14??
Please let me know.
"Mike H" wrote:
Hi again,
IMHO I'd enter times correctly but perhaps that's just me!! This seems to
do
what you want
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 4 And Target.Column <= 7 Then
If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then
hournum = Val(Left(Target, Len(Target) - 1))
minnum = Val(Mid(Target, 3, Len(Target) - 1))
If Right(Target, 1) = "p" And hournum < 12 Then hournum =
hournum +
12
If Right(Target, 1) = "a" And hournum = 12 Then hournum =
hournum - 12
Target.Value = TimeSerial(hournum, minnum, 0)
'Target.NumberFormat = "[$-409]h:mm AM/PM;@"
Target.NumberFormat = "h:mm AM/PM"
Else
Target.Value = ""
Target.Select
MsgBox "Enter a or p!"
End If
End If
Application.EnableEvents = True
End Sub
Mike
"art" wrote:
Thanks, but I need two more things.
First when I enter 12a it turns to 12:00 PM and 12p turns to 12:00AM.
Also, can I do this vba for columns D thru G?
Please let me know.
"Mike H" wrote:
Try this
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then 'column A
If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then
hournum = Val(Left(Target, Len(Target) - 1))
minnum = Val(Mid(Target, 3, Len(Target) - 1))
If Right(Target, 1) = "p" Then hournum = hournum + 12
Target.Value = TimeSerial(hournum, minnum, 0)
Target.NumberFormat = "[$-409]h:mm AM/PM;@"
Else
Target.Value = ""
Target.Select
MsgBox "Enter a or p!"
End If
End If
Application.EnableEvents = True
End Sub
Mike
"art" wrote:
Hello:
I have the vba code below but I need some changes. I want to make
that when
I enter in a cell 5a it should automatically format it to 5:00 AM
and when I
enter 7p it should format it to 7:00 PM and so on. However I also
need that
when I enter 5:05a it should format to 5:05 AM. Is there a way to
do this?
Please let me know.
The code I have is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then 'column A
If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then
hournum = Val(Left(Target, Len(Target) - 1))
If Right(Target, 1) = "p" Then hournum = hournum + 12
Target.Value = TimeSerial(hournum, 0, 0)
Target.NumberFormat = "[$-409]h:mm AM/PM;@"
Else
Target.Value = ""
Target.Select
MsgBox "Enter a or p!"
End If
End If
Application.EnableEvents = True
End Sub
|