| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation of time in code
I have the following code in a worksheet that makes
entering times very easy. Example: It converts an entry of 1500 to 15:00. I need to limit the entry of times to between 00:01 and 18:00. I need help with some additional code to restrict entries to between these two times. Thanks Steve Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range ("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation of time in code
Could you add something like this to the top of your code:
If IsNumeric(Target.Value) = False _ Or (Target.Value < 1 _ Or Target.Value 1500) Then With Application .EnableEvents = False .Undo .EnableEvents = True MsgBox "no. no. no." Exit Sub End With End If (Check it before you do the conversion.) Steve Rolls wrote: I have the following code in a worksheet that makes entering times very easy. Example: It converts an entry of 1500 to 15:00. I need to limit the entry of times to between 00:01 and 18:00. I need help with some additional code to restrict entries to between these two times. Thanks Steve Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range ("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation of time in code
Steve,
The entire procedure should be as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Range("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select If TimeValue(TimeStr) < TimeValue("00:00:01") Or _ TimeValue(TimeStr) TimeValue("18:00:00") Then MsgBox "time outside interval" .NumberFormat = "General" Application.EnableEvents = True Exit Sub End If .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Steve Rolls" wrote in message ... Thanks Chip, I can't remember exactly where I got the original code from, but since you recognized it had to be from your wonderful website. I tried inserting your additional code per your instructions but I must not have done something correctly between where I inserted it and and End Sub. Can you help me a bit more with what should be entered after your additional code ? Thanks again, Steve -----Original Message----- Steve, That code looks familiar. To limit the input time to a specific interval, add the following code immediately following the End Select statement: If TimeValue(TimeStr) < TimeValue("00:00:01") Or _ TimeValue(TimeStr) TimeValue("18:00:00") Then MsgBox "time outside interval" .NumberFormat = "General" Application.EnableEvents = True Exit Sub End If -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Steve Rolls" wrote in message ... I have the following code in a worksheet that makes entering times very easy. Example: It converts an entry of 1500 to 15:00. I need to limit the entry of times to between 00:01 and 18:00. I need help with some additional code to restrict entries to between these two times. Thanks Steve Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range ("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation of time in code
Chip, this code worked perfectly ! Thank you Very Much !
When I brought this to the person I was working on this for they now asked me if I could make the validation portion work on the range h11:h500 only, yet keep the functionality of converting 1500 (example) to 15:00 on the other two ranges (d11:e500 and h2:i5) Now a am totally lost on how to do this Steve -----Original Message----- Steve, The entire procedure should be as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Range("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select If TimeValue(TimeStr) < TimeValue("00:00:01") Or _ TimeValue(TimeStr) TimeValue("18:00:00") Then MsgBox "time outside interval" .NumberFormat = "General" Application.EnableEvents = True Exit Sub End If .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Steve Rolls" wrote in message ... Thanks Chip, I can't remember exactly where I got the original code from, but since you recognized it had to be from your wonderful website. I tried inserting your additional code per your instructions but I must not have done something correctly between where I inserted it and and End Sub. Can you help me a bit more with what should be entered after your additional code ? Thanks again, Steve -----Original Message----- Steve, That code looks familiar. To limit the input time to a specific interval, add the following code immediately following the End Select statement: If TimeValue(TimeStr) < TimeValue("00:00:01") Or _ TimeValue(TimeStr) TimeValue("18:00:00") Then MsgBox "time outside interval" .NumberFormat = "General" Application.EnableEvents = True Exit Sub End If -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Steve Rolls" wrote in message ... I have the following code in a worksheet that makes entering times very easy. Example: It converts an entry of 1500 to 15:00. I need to limit the entry of times to between 00:01 and 18:00. I need help with some additional code to restrict entries to between these two times. Thanks Steve Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range ("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation of time in code
Steve,
Try the following Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Range("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select If Not Application.Intersect(Range("H11:H500"), _ Target) Is Nothing Then If TimeValue(TimeStr) < TimeValue("00:00:01") Or _ TimeValue(TimeStr) TimeValue("18:00:00") Then MsgBox "time outside interval" .NumberFormat = "General" Application.EnableEvents = True Exit Sub End If End If .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Steve Rolls" wrote in message ... Chip, this code worked perfectly ! Thank you Very Much ! When I brought this to the person I was working on this for they now asked me if I could make the validation portion work on the range h11:h500 only, yet keep the functionality of converting 1500 (example) to 15:00 on the other two ranges (d11:e500 and h2:i5) Now a am totally lost on how to do this Steve -----Original Message----- Steve, The entire procedure should be as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Range("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select If TimeValue(TimeStr) < TimeValue("00:00:01") Or _ TimeValue(TimeStr) TimeValue("18:00:00") Then MsgBox "time outside interval" .NumberFormat = "General" Application.EnableEvents = True Exit Sub End If .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Steve Rolls" wrote in message ... Thanks Chip, I can't remember exactly where I got the original code from, but since you recognized it had to be from your wonderful website. I tried inserting your additional code per your instructions but I must not have done something correctly between where I inserted it and and End Sub. Can you help me a bit more with what should be entered after your additional code ? Thanks again, Steve -----Original Message----- Steve, That code looks familiar. To limit the input time to a specific interval, add the following code immediately following the End Select statement: If TimeValue(TimeStr) < TimeValue("00:00:01") Or _ TimeValue(TimeStr) TimeValue("18:00:00") Then MsgBox "time outside interval" .NumberFormat = "General" Application.EnableEvents = True Exit Sub End If -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Steve Rolls" wrote in message ... I have the following code in a worksheet that makes entering times very easy. Example: It converts an entry of 1500 to 15:00. I need to limit the entry of times to between 00:01 and 18:00. I need help with some additional code to restrict entries to between these two times. Thanks Steve Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range ("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Validation of time in code
Chip, Thank You VERY MUCH ! It works perfectly. I will
have some fun reviewing this code and trying to figure it out. In the meantime you made me look good ! Steve -----Original Message----- Steve, Try the following Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Range("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select If Not Application.Intersect(Range("H11:H500"), _ Target) Is Nothing Then If TimeValue(TimeStr) < TimeValue("00:00:01") Or _ TimeValue(TimeStr) TimeValue("18:00:00") Then MsgBox "time outside interval" .NumberFormat = "General" Application.EnableEvents = True Exit Sub End If End If .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Steve Rolls" wrote in message ... Chip, this code worked perfectly ! Thank you Very Much ! When I brought this to the person I was working on this for they now asked me if I could make the validation portion work on the range h11:h500 only, yet keep the functionality of converting 1500 (example) to 15:00 on the other two ranges (d11:e500 and h2:i5) Now a am totally lost on how to do this Steve -----Original Message----- Steve, The entire procedure should be as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Range("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select If TimeValue(TimeStr) < TimeValue("00:00:01") Or _ TimeValue(TimeStr) TimeValue("18:00:00") Then MsgBox "time outside interval" .NumberFormat = "General" Application.EnableEvents = True Exit Sub End If .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Steve Rolls" wrote in message ... Thanks Chip, I can't remember exactly where I got the original code from, but since you recognized it had to be from your wonderful website. I tried inserting your additional code per your instructions but I must not have done something correctly between where I inserted it and and End Sub. Can you help me a bit more with what should be entered after your additional code ? Thanks again, Steve -----Original Message----- Steve, That code looks familiar. To limit the input time to a specific interval, add the following code immediately following the End Select statement: If TimeValue(TimeStr) < TimeValue("00:00:01") Or _ TimeValue(TimeStr) TimeValue("18:00:00") Then MsgBox "time outside interval" .NumberFormat = "General" Application.EnableEvents = True Exit Sub End If -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Steve Rolls" wrote in message ... I have the following code in a worksheet that makes entering times very easy. Example: It converts an entry of 1500 to 15:00. I need to limit the entry of times to between 00:01 and 18:00. I need help with some additional code to restrict entries to between these two times. Thanks Steve Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range ("d11:e500,h11:h500,h2:i5")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right (.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right (.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| date code validation | Excel Discussion (Misc queries) | |||
| Zip Code Validation - length =5 or 9 | Excel Discussion (Misc queries) | |||
| Validation of Postal Code | Excel Worksheet Functions | |||
| time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
| time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
Linear Mode
