ExcelBanter - View Single Post

BizMark wrote:
Here's a little routine I've just written which I think will solve your
problem.

Paste this into the 'ThisWorkbook' module of your workbook.
Replace GT with the Greater Than symbol and LT with the Less Than
symbol.

Note that the routine will only operate on cells formatted as 'Date',
so you can choose which cells get the treatment and which ones don't.

Code:============================================= ======

Public xLastRange As Excel.Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Excel.Range)
If Not IsEmpty(xLastRange) And TypeName(xLastRange) GT LT "Nothing"
Then
If xLastRange.Cells.Count = 1 Then
If InStr(1, xLastRange.NumberFormat, ":") GT 0 Then
If Format(xLastRange.Value, "hh:mm") LT GT
xLastRange.Text Then
tTimeAsDigits = Format(xLastRange.Value, "0000")
tTimeAsValue = Left(tTimeAsDigits, 2) & ":" &
Mid(tTimeAsDigits, 3, 2) & ":00"
If IsDate(tTimeAsValue) Then
dTimeAsValue = TimeValue(tTimeAsValue)
xLastRange.Value = dTimeAsValue
Else
xLastRange.Value = "#TIME?"
Beep
MsgBox ("Invalid time entered. Please
re-enter.")
End If
End If
End If
End If
End If

Set xLastRange = Target
End Sub

Hope this helps.
Regards,
BizMark

Thanks