time format

A

art

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
 
M

Mike H

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
 
A

art

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 said:
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 said:
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
Click to expand...
Click to expand...
 
M

Mike H

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
A

art

Is it possible to make this vba just for certain cells, like from D8 thrug14??

Please let me know.




Mike H said:
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 said:
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 said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
M

Mike H

Yes that's possible

Use as a first line to prevent errors for multiple selections/blanks
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

Works on a single cell
If Target.Address = "$A$1" Then
'do things
End if

Works on a range
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
'do things
End if

Discontiguous range
If Not Intersect(Target, Range("A1,B1,C1")) Is Nothing Then
'do things
End if


Mike

art said:
Is it possible to make this vba just for certain cells, like from D8 thrug14??

Please let me know.




Mike H said:
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 said:
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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

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 said:
Is it possible to make this vba just for certain cells, like from D8
thrug14??

Please let me know.




Mike H said:
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 said:
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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
M

Mike H

Rick,

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

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 said:
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 said:
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 said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
A

art

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 said:
Rick,

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

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 said:
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 said:
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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

Whoops! Typo alert. The code should be this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim T As String
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
T = Replace(.Value, " ", "")
.Value = Replace(T, "a", ":00 AM", , , vbTextCompare)
.Value = Replace(T, "p", ":00 PM", , , vbTextCompare)
End If
End If
End With
Application.EnableEvents = True
End Sub

I accidentally replaced "p" with ":00 AM" instead of ":00 PM". I also took
the opportunity to add an extra measure of protection for the user entering
too many spaces (not completely sure it is required, but adding the extra
code insures it doesn't matter). The code should now function correctly with
12p or 10:08p.

--
Rick (MVP - Excel)


Mike H said:
Rick,

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

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 said:
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 said:
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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

Just correcting the code here to account for the typo I corrected in my
other sub-thread...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim T As String
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
T = Replace(.Value, " ", "")
.Value = Replace(T, "a", ":00 AM", , , vbTextCompare)
.Value = Replace(T, "p", ":00 PM", , , vbTextCompare)
End If
End If
End With
Application.EnableEvents = True
End Sub

--
Rick (MVP - Excel)


Rick Rothstein said:
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 said:
Is it possible to make this vba just for certain cells, like from D8
thrug14??

Please let me know.




Mike H said:
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

:

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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
M

Mike H

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 said:
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 said:
Rick,

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

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 said:
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)


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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
M

Mike H

10:08p tested?

Rick Rothstein said:
Whoops! Typo alert. The code should be this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim T As String
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
T = Replace(.Value, " ", "")
.Value = Replace(T, "a", ":00 AM", , , vbTextCompare)
.Value = Replace(T, "p", ":00 PM", , , vbTextCompare)
End If
End If
End With
Application.EnableEvents = True
End Sub

I accidentally replaced "p" with ":00 AM" instead of ":00 PM". I also took
the opportunity to add an extra measure of protection for the user entering
too many spaces (not completely sure it is required, but adding the extra
code insures it doesn't matter). The code should now function correctly with
12p or 10:08p.

--
Rick (MVP - Excel)


Mike H said:
Rick,

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

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 said:
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)


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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
A

art

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

Mike H said:
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 said:
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 said:
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

:

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)


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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

Well, it had been tested at one point in my developing the code... and
whenever it was that I had tested it, it did work back then... but obviously
I changed something since that test and it no longer works. Okay then, how
about this code instead (it seems to work correctly)?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim T As String
If Target.Count > 1 Then Exit Sub
With Target
If Not Intersect(Target, Range("D:G")) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
T = Replace(.Value, "a", " AM", , , vbTextCompare)
T = Replace(T, "p", " PM", , , vbTextCompare)
.Value = WorksheetFunction.Trim(T)
End If
End With
Whoops:
Application.EnableEvents = True
End Sub

Note that I also straightened out a problem with when the EnableEvents
statement got executed. Also, I had forgotten to include the On Error Goto
test (you should also consider incorporating this into your code too).
Thanks for looking at the code I posted and for keeping me honest.

--
Rick (MVP - Excel)


Mike H said:
10:08p tested?

Rick Rothstein said:
Whoops! Typo alert. The code should be this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim T As String
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
T = Replace(.Value, " ", "")
.Value = Replace(T, "a", ":00 AM", , , vbTextCompare)
.Value = Replace(T, "p", ":00 PM", , , vbTextCompare)
End If
End If
End With
Application.EnableEvents = True
End Sub

I accidentally replaced "p" with ":00 AM" instead of ":00 PM". I also
took
the opportunity to add an extra measure of protection for the user
entering
too many spaces (not completely sure it is required, but adding the extra
code insures it doesn't matter). The code should now function correctly
with
12p or 10:08p.

--
Rick (MVP - Excel)


Mike H said:
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

:

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)


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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
M

Mike H

Bingo!!! we are not worthy:) Nice code.

Mike

Rick Rothstein said:
Well, it had been tested at one point in my developing the code... and
whenever it was that I had tested it, it did work back then... but obviously
I changed something since that test and it no longer works. Okay then, how
about this code instead (it seems to work correctly)?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim T As String
If Target.Count > 1 Then Exit Sub
With Target
If Not Intersect(Target, Range("D:G")) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
T = Replace(.Value, "a", " AM", , , vbTextCompare)
T = Replace(T, "p", " PM", , , vbTextCompare)
.Value = WorksheetFunction.Trim(T)
End If
End With
Whoops:
Application.EnableEvents = True
End Sub

Note that I also straightened out a problem with when the EnableEvents
statement got executed. Also, I had forgotten to include the On Error Goto
test (you should also consider incorporating this into your code too).
Thanks for looking at the code I posted and for keeping me honest.

--
Rick (MVP - Excel)


Mike H said:
10:08p tested?

Rick Rothstein said:
Whoops! Typo alert. The code should be this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim T As String
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
T = Replace(.Value, " ", "")
.Value = Replace(T, "a", ":00 AM", , , vbTextCompare)
.Value = Replace(T, "p", ":00 PM", , , vbTextCompare)
End If
End If
End With
Application.EnableEvents = True
End Sub

I accidentally replaced "p" with ":00 AM" instead of ":00 PM". I also
took
the opportunity to add an extra measure of protection for the user
entering
too many spaces (not completely sure it is required, but adding the extra
code insures it doesn't matter). The code should now function correctly
with
12p or 10:08p.

--
Rick (MVP - Excel)


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

:

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)


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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

Thanks. I knew I was on a good track with the string replacement method, but
I was surprised how long it took me to get it right. Thank you for keeping
on me for my postings... with your help, you pushed me onto a decent
solution (which, as it turns out, ended up being simpler than my initial
idea was).

--
Rick (MVP - Excel)


Mike H said:
Bingo!!! we are not worthy:) Nice code.

Mike

Rick Rothstein said:
Well, it had been tested at one point in my developing the code... and
whenever it was that I had tested it, it did work back then... but
obviously
I changed something since that test and it no longer works. Okay then,
how
about this code instead (it seems to work correctly)?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim T As String
If Target.Count > 1 Then Exit Sub
With Target
If Not Intersect(Target, Range("D:G")) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
T = Replace(.Value, "a", " AM", , , vbTextCompare)
T = Replace(T, "p", " PM", , , vbTextCompare)
.Value = WorksheetFunction.Trim(T)
End If
End With
Whoops:
Application.EnableEvents = True
End Sub

Note that I also straightened out a problem with when the EnableEvents
statement got executed. Also, I had forgotten to include the On Error
Goto
test (you should also consider incorporating this into your code too).
Thanks for looking at the code I posted and for keeping me honest.

--
Rick (MVP - Excel)


Mike H said:
10:08p tested?

:

Whoops! Typo alert. The code should be this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim T As String
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
T = Replace(.Value, " ", "")
.Value = Replace(T, "a", ":00 AM", , , vbTextCompare)
.Value = Replace(T, "p", ":00 PM", , , vbTextCompare)
End If
End If
End With
Application.EnableEvents = True
End Sub

I accidentally replaced "p" with ":00 AM" instead of ":00 PM". I also
took
the opportunity to add an extra measure of protection for the user
entering
too many spaces (not completely sure it is required, but adding the
extra
code insures it doesn't matter). The code should now function
correctly
with
12p or 10:08p.

--
Rick (MVP - Excel)


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

:

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)


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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
A

art

Thanks for responding, but I decided that the best way is to enter the time
without the : symbol. Like 944a. Can you make the code for that please? I
found a page that lets me do that but it goes until 24 hours and does not let
me enter p or a. Please help.





Mike H said:
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 said:
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 said:
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

:

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)


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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
A

art

Here is the web site:

http://www.cpearson.com/excel/DateTimeEntry.htm








Mike H said:
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 said:
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 said:
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

:

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)


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.



:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

please help.is it possible re-edit macro to convert text like1530-2200 to time format automatically? 1
Out of Stack Space error 2
Trying to run excel vba code when cell value changes from empty to entered value. 1
Time with VBA code 8
VBA 10
Entering 24 Hour Clock without entering : 5
Counting clicks 5
Excel Already selected values from DropDown list dynamically updating on multiple sheets 10

Top