Time Gentlemen Please
 

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

Time Gentlemen Please
 

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" wrote in message
...
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

Time Gentlemen Please
 

Thank you Bob.

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

Don.



"Bob Phillips" wrote in message
...

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" wrote in message
...
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