Time Gentlemen Please

[Don Lloyd]

Hi All and Happy Easter.

I have a list of sequential times in a column, formatted as hh:mm

Object:
To allow a date to be changed by the user.

Safeguards:
1. To ensure that the entry is a valid time.
2. The time entered must be later than the one in the row above.

Had the list been Dates, I'd be OK, but I can't get the hang of handling
Time.

VBA solution preferred

regards,
Don

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
If .Row > 1 Then
If .Value > 1 Or _
.Value <= .Offset(-1, 0).Value Then
MsgBox "Invalid time"
.Value = ""
End If
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Don Lloyd" <[email protected]> wrote in message
news:[email protected]...
> Hi All and Happy Easter.
>
> I have a list of sequential times in a column, formatted as hh:mm
>
> Object:
> To allow a date to be changed by the user.
>
> Safeguards:
> 1. To ensure that the entry is a valid time.
> 2. The time entered must be later than the one in the row above.
>
> Had the list been Dates, I'd be OK, but I can't get the hang of handling
> Time.
>
> VBA solution preferred
>
> regards,
> Don
>
>

[Don Lloyd]

Thank you Bob.

It's the Value part that I couldn't fathom

Don.



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> With Target
> If .Column = 1 Then
> If .Row > 1 Then
> If .Value > 1 Or _
> .Value <= .Offset(-1, 0).Value Then
> MsgBox "Invalid time"
> .Value = ""
> End If
> End If
> End If
> End With
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Don Lloyd" <[email protected]> wrote in message
> news:[email protected]...
>> Hi All and Happy Easter.
>>
>> I have a list of sequential times in a column, formatted as hh:mm
>>
>> Object:
>> To allow a date to be changed by the user.
>>
>> Safeguards:
>> 1. To ensure that the entry is a valid time.
>> 2. The time entered must be later than the one in the row above.
>>
>> Had the list been Dates, I'd be OK, but I can't get the hang of handling
>> Time.
>>
>> VBA solution preferred
>>
>> regards,
>> Don
>>
>>
>
>

[widemonk]

Can you no simply use Cell Validation?

Allow data as time, greater than, previous cell (relative, so dont use $)