If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
time format
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" wrote in message ... 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 |
#13
|
|||
|
|||
time format
I showed you how to do that in my previous post. make this the first line
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub Mike "art" wrote: Thank you all. I had to combine in order to achieve what I wanted. I used the following: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("D8:G14")) Is Nothing Then If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) minnum = Val(Mid(Target, 3, Len(Target) - 1)) If Right(Target, 1) = "p" And hournum 12 Then hournum = hournum + 12 If Right(Target, 1) = "a" And hournum = 12 Then hournum = hournum - 12 Target.Value = TimeSerial(hournum, minnum, 0) 'Target.NumberFormat = "[$-409]h:mm AM/PM;@" Target.NumberFormat = "h:mm AM/PM" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub However, when I delete anything, the pop up window comes up to enter a "a" or a "p". Is it possible to make that it shouldn't popup when you delete? "Mike H" wrote: Rick, It's not my approach .Note my comment:- IMHO I'd enter times correctly but perhaps that's just me!! I think the multiple types of input the OP propsed dictate the approach. Did you test yours for 12p or 10:08p? Mine fell over for the latter until I ammended this line minnum = Val(Mid(Target, InStr(1, Target, ":") + 1, Len(Target) - 1)) I go back to my original proposition, enter times correctly. Mike "Rick Rothstein" wrote: Here is code that is different from Mike's approach (it allows you to enter times in the format you asked about or in real Excel times (your choice) and it works in the columns you asked about... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub Application.EnableEvents = False With Target If Not Intersect(Target, Range("D:G")) Is Nothing Then If InStr(.Value, ":") = 0 Then .Value = Replace(.Value, "a", ":00 AM", , , vbTextCompare) .Value = Replace(.Value, "p", ":00 AM", , , vbTextCompare) End If End If End With Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "art" wrote in message ... Thanks, but I need two more things. First when I enter 12a it turns to 12:00 PM and 12p turns to 12:00AM. Also, can I do this vba for columns D thru G? Please let me know. "Mike H" wrote: Try this Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then 'column A If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) minnum = Val(Mid(Target, 3, Len(Target) - 1)) If Right(Target, 1) = "p" Then hournum = hournum + 12 Target.Value = TimeSerial(hournum, minnum, 0) Target.NumberFormat = "[$-409]h:mm AM/PM;@" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub Mike "art" wrote: Hello: I have the vba code below but I need some changes. I want to make that when I enter in a cell 5a it should automatically format it to 5:00 AM and when I enter 7p it should format it to 7:00 PM and so on. However I also need that when I enter 5:05a it should format to 5:05 AM. Is there a way to do this? Please let me know. The code I have is as follows: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then 'column A If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) If Right(Target, 1) = "p" Then hournum = hournum + 12 Target.Value = TimeSerial(hournum, 0, 0) Target.NumberFormat = "[$-409]h:mm AM/PM;@" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub |
#14
|
|||
|
|||
time format
10:08p tested?
"Rick Rothstein" wrote: 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 |
#15
|
|||
|
|||
time format
It doesnt work. Should I add it to the first line?
"Mike H" wrote: I showed you how to do that in my previous post. make this the first line If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub Mike "art" wrote: Thank you all. I had to combine in order to achieve what I wanted. I used the following: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("D8:G14")) Is Nothing Then If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) minnum = Val(Mid(Target, 3, Len(Target) - 1)) If Right(Target, 1) = "p" And hournum 12 Then hournum = hournum + 12 If Right(Target, 1) = "a" And hournum = 12 Then hournum = hournum - 12 Target.Value = TimeSerial(hournum, minnum, 0) 'Target.NumberFormat = "[$-409]h:mm AM/PM;@" Target.NumberFormat = "h:mm AM/PM" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub However, when I delete anything, the pop up window comes up to enter a "a" or a "p". Is it possible to make that it shouldn't popup when you delete? "Mike H" wrote: Rick, It's not my approach .Note my comment:- IMHO I'd enter times correctly but perhaps that's just me!! I think the multiple types of input the OP propsed dictate the approach. Did you test yours for 12p or 10:08p? Mine fell over for the latter until I ammended this line minnum = Val(Mid(Target, InStr(1, Target, ":") + 1, Len(Target) - 1)) I go back to my original proposition, enter times correctly. Mike "Rick Rothstein" wrote: Here is code that is different from Mike's approach (it allows you to enter times in the format you asked about or in real Excel times (your choice) and it works in the columns you asked about... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub Application.EnableEvents = False With Target If Not Intersect(Target, Range("D:G")) Is Nothing Then If InStr(.Value, ":") = 0 Then .Value = Replace(.Value, "a", ":00 AM", , , vbTextCompare) .Value = Replace(.Value, "p", ":00 AM", , , vbTextCompare) End If End If End With Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "art" wrote in message ... Thanks, but I need two more things. First when I enter 12a it turns to 12:00 PM and 12p turns to 12:00AM. Also, can I do this vba for columns D thru G? Please let me know. "Mike H" wrote: Try this Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then 'column A If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) minnum = Val(Mid(Target, 3, Len(Target) - 1)) If Right(Target, 1) = "p" Then hournum = hournum + 12 Target.Value = TimeSerial(hournum, minnum, 0) Target.NumberFormat = "[$-409]h:mm AM/PM;@" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub Mike "art" wrote: Hello: I have the vba code below but I need some changes. I want to make that when I enter in a cell 5a it should automatically format it to 5:00 AM and when I enter 7p it should format it to 7:00 PM and so on. However I also need that when I enter 5:05a it should format to 5:05 AM. Is there a way to do this? Please let me know. The code I have is as follows: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then 'column A If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) If Right(Target, 1) = "p" Then hournum = hournum + 12 Target.Value = TimeSerial(hournum, 0, 0) Target.NumberFormat = "[$-409]h:mm AM/PM;@" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub |
#16
|
|||
|
|||
time format
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" wrote in message ... 10:08p tested? "Rick Rothstein" wrote: 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 |
#17
|
|||
|
|||
time format
Bingo!!! we are not worthy Nice code.
Mike "Rick Rothstein" wrote: 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" wrote in message ... 10:08p tested? "Rick Rothstein" wrote: 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 |
#18
|
|||
|
|||
time format
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" wrote in message ... Bingo!!! we are not worthy Nice code. Mike "Rick Rothstein" wrote: 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" wrote in message ... 10:08p tested? "Rick Rothstein" wrote: 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 |
#19
|
|||
|
|||
time format
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" wrote: I showed you how to do that in my previous post. make this the first line If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub Mike "art" wrote: Thank you all. I had to combine in order to achieve what I wanted. I used the following: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("D8:G14")) Is Nothing Then If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) minnum = Val(Mid(Target, 3, Len(Target) - 1)) If Right(Target, 1) = "p" And hournum 12 Then hournum = hournum + 12 If Right(Target, 1) = "a" And hournum = 12 Then hournum = hournum - 12 Target.Value = TimeSerial(hournum, minnum, 0) 'Target.NumberFormat = "[$-409]h:mm AM/PM;@" Target.NumberFormat = "h:mm AM/PM" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub However, when I delete anything, the pop up window comes up to enter a "a" or a "p". Is it possible to make that it shouldn't popup when you delete? "Mike H" wrote: Rick, It's not my approach .Note my comment:- IMHO I'd enter times correctly but perhaps that's just me!! I think the multiple types of input the OP propsed dictate the approach. Did you test yours for 12p or 10:08p? Mine fell over for the latter until I ammended this line minnum = Val(Mid(Target, InStr(1, Target, ":") + 1, Len(Target) - 1)) I go back to my original proposition, enter times correctly. Mike "Rick Rothstein" wrote: Here is code that is different from Mike's approach (it allows you to enter times in the format you asked about or in real Excel times (your choice) and it works in the columns you asked about... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub Application.EnableEvents = False With Target If Not Intersect(Target, Range("D:G")) Is Nothing Then If InStr(.Value, ":") = 0 Then .Value = Replace(.Value, "a", ":00 AM", , , vbTextCompare) .Value = Replace(.Value, "p", ":00 AM", , , vbTextCompare) End If End If End With Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "art" wrote in message ... Thanks, but I need two more things. First when I enter 12a it turns to 12:00 PM and 12p turns to 12:00AM. Also, can I do this vba for columns D thru G? Please let me know. "Mike H" wrote: Try this Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then 'column A If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) minnum = Val(Mid(Target, 3, Len(Target) - 1)) If Right(Target, 1) = "p" Then hournum = hournum + 12 Target.Value = TimeSerial(hournum, minnum, 0) Target.NumberFormat = "[$-409]h:mm AM/PM;@" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub Mike "art" wrote: Hello: I have the vba code below but I need some changes. I want to make that when I enter in a cell 5a it should automatically format it to 5:00 AM and when I enter 7p it should format it to 7:00 PM and so on. However I also need that when I enter 5:05a it should format to 5:05 AM. Is there a way to do this? Please let me know. The code I have is as follows: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then 'column A If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) If Right(Target, 1) = "p" Then hournum = hournum + 12 Target.Value = TimeSerial(hournum, 0, 0) Target.NumberFormat = "[$-409]h:mm AM/PM;@" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub |
#20
|
|||
|
|||
time format
Here is the web site:
http://www.cpearson.com/excel/DateTimeEntry.htm "Mike H" wrote: I showed you how to do that in my previous post. make this the first line If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub Mike "art" wrote: Thank you all. I had to combine in order to achieve what I wanted. I used the following: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Intersect(Target, Range("D8:G14")) Is Nothing Then If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) minnum = Val(Mid(Target, 3, Len(Target) - 1)) If Right(Target, 1) = "p" And hournum 12 Then hournum = hournum + 12 If Right(Target, 1) = "a" And hournum = 12 Then hournum = hournum - 12 Target.Value = TimeSerial(hournum, minnum, 0) 'Target.NumberFormat = "[$-409]h:mm AM/PM;@" Target.NumberFormat = "h:mm AM/PM" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub However, when I delete anything, the pop up window comes up to enter a "a" or a "p". Is it possible to make that it shouldn't popup when you delete? "Mike H" wrote: Rick, It's not my approach .Note my comment:- IMHO I'd enter times correctly but perhaps that's just me!! I think the multiple types of input the OP propsed dictate the approach. Did you test yours for 12p or 10:08p? Mine fell over for the latter until I ammended this line minnum = Val(Mid(Target, InStr(1, Target, ":") + 1, Len(Target) - 1)) I go back to my original proposition, enter times correctly. Mike "Rick Rothstein" wrote: Here is code that is different from Mike's approach (it allows you to enter times in the format you asked about or in real Excel times (your choice) and it works in the columns you asked about... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub Application.EnableEvents = False With Target If Not Intersect(Target, Range("D:G")) Is Nothing Then If InStr(.Value, ":") = 0 Then .Value = Replace(.Value, "a", ":00 AM", , , vbTextCompare) .Value = Replace(.Value, "p", ":00 AM", , , vbTextCompare) End If End If End With Application.EnableEvents = True End Sub -- Rick (MVP - Excel) "art" wrote in message ... Thanks, but I need two more things. First when I enter 12a it turns to 12:00 PM and 12p turns to 12:00AM. Also, can I do this vba for columns D thru G? Please let me know. "Mike H" wrote: Try this Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then 'column A If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) minnum = Val(Mid(Target, 3, Len(Target) - 1)) If Right(Target, 1) = "p" Then hournum = hournum + 12 Target.Value = TimeSerial(hournum, minnum, 0) Target.NumberFormat = "[$-409]h:mm AM/PM;@" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub Mike "art" wrote: Hello: I have the vba code below but I need some changes. I want to make that when I enter in a cell 5a it should automatically format it to 5:00 AM and when I enter 7p it should format it to 7:00 PM and so on. However I also need that when I enter 5:05a it should format to 5:05 AM. Is there a way to do this? Please let me know. The code I have is as follows: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then 'column A If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then hournum = Val(Left(Target, Len(Target) - 1)) If Right(Target, 1) = "p" Then hournum = hournum + 12 Target.Value = TimeSerial(hournum, 0, 0) Target.NumberFormat = "[$-409]h:mm AM/PM;@" Else Target.Value = "" Target.Select MsgBox "Enter a or p!" End If End If Application.EnableEvents = True End Sub |
Thread Tools | |
Display Modes | |
|
|