time format
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
|