It doesnt work. Should I add it to the first line?

"Mike H" wrote:

I showed you how to do that in my previous post. make this the first line

If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub

Mike

"art" wrote:

Thank you all. I had to combine in order to achieve what I wanted.

I used the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("D8:G14")) Is Nothing 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

However, when I delete anything, the pop up window comes up to enter a "a"
or a "p". Is it possible to make that it shouldn't popup when you delete?



"Mike H" wrote:

Rick,

It's not my approach :) .Note my comment:-
IMHO I'd enter times correctly but perhaps that's just me!!

I think the multiple types of input the OP propsed dictate the approach.
Did you test yours for 12p or 10:08p?

Mine fell over for the latter until I ammended this line
minnum = Val(Mid(Target, InStr(1, Target, ":") + 1, Len(Target) - 1))

I go back to my original proposition, enter times correctly.

Mike

"Rick Rothstein" wrote:

Here is code that is different from Mike's approach (it allows you to enter
times in the format you asked about or in real Excel times (your choice) and
it works in the columns 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("D:G")) 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

--
Rick (MVP - Excel)


"art" wrote in message
...
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