| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
time format
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time format
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time format
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time format
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time format
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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time format
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time format
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time format
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time format
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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding time in 24 hour format to produce hours in decimal format | Excel Worksheet Functions | |||
| Convert time stored as decimalised number to time format | New Users to Excel | |||
| Converting a time format to a String format in Excel | Excel Discussion (Misc queries) | |||
| convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
| Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions | |||
Hybrid Mode
