Date Time Question

[Guest]

I have the following on my spreadsheet:

A1 = Date
A5 = Begin Time
A6 = End Time
A7 = A1+A5 'Giving me the Beginning Date & Time
A8 = A1+A6 'Giving me the End Date & Time

What I want to do is whenever the user enters a time in Cell A9, either a
data validation or vba check that time entered is between the value in Cell
A7 and A8. The assumption is that the value in Cell A9 is A9+A1 but I only
want the user to only enter the time. It is probably easier just to have a
format of Date/Time throughout but I am constraint by the design format. I
tried with the validation with a custom formula: = AND(A9+A1>A7, A9+A1<A8).
This just gave me a error for any number I entered. And I tried a event
procedure:

If Target.Column = 15 And Target.Row = 10 Then
If Target.Value + Target.Offset(-9, 2).Value >
Target.Offset(-1, 0).Value And Target.Value + Target.Offset(-9, 2).Value >
Target.Offset(-1, 1) Then
Target.Value = Target.Value
Else
MsgBox ("Time entered does not fall within work shift.")
Target.Value = " "
Exit Sub
End If
End If
End Sub

Cell

 

[Sox]

I have the following on my spreadsheet:

A1 = Date
A5 = Begin Time
A6 = End Time
A7 = A1+A5 'Giving me the Beginning Date & Time
A8 = A1+A6 'Giving me the End Date & Time

What I want to do is whenever the user enters a time in Cell A9, either a
data validation or vba check that time entered is between the value in
Cell
A7 and A8. The assumption is that the value in Cell A9 is A9+A1 but I
only
want the user to only enter the time. It is probably easier just to have
a
format of Date/Time throughout but I am constraint by the design format.
I
tried with the validation with a custom formula: = AND(A9+A1>A7,
A9+A1<A8).
This just gave me a error for any number I entered. And I tried a event
procedure:

If Target.Column = 15 And Target.Row = 10 Then
If Target.Value + Target.Offset(-9, 2).Value >
Target.Offset(-1, 0).Value And Target.Value + Target.Offset(-9, 2).Value >
Target.Offset(-1, 1) Then
Target.Value = Target.Value
Else
MsgBox ("Time entered does not fall within work
shift.")
Target.Value = " "
Exit Sub
End If
End If
End Sub

Cell

Private Sub Worksheet_Change(ByVal target As Range)

myRow = target.Row ' Remember row of changed cell
myCol = target.Column ' Remember column of changed cell
myValue = target.Value ' Remember user entered time

If myValue = "" Then Exit Sub ' If cell null by this routine, do not get
into endless loop

' No sense doing this check if cell A9 is not the one that changed
If myRow = 9 And myCol = 1 Then

' Assumption is that value to check is the date in cell A1 PLUS the user
entered value
chkValue = target.Value + Cells(1, 1).Value

' Make a string for later use if this is a bad value
myText = Format(myValue, "hh:mm AMPM")

' Use the worksheet to get the minimum and maximum acceptable values
minValue = Cells(7, 1).Value
maxValue = Cells(8, 1).Value

' Now check to see if user input is good -- if not, set to null string
and prompt user
If chkValue < minValue Then
MsgBox "Time " & myText & " is too early - please re-enter"
Cells(9, 1).Value = ""
Else
If chkValue > maxValue Then
MsgBox "Time " & myText & " is too late -- please re-enter."
End If
End If
End If

End Sub