Bob,
I have today's date in C7, a 'name' in D7 and 'Y' in E7 and still get reply
"varify sweep completed"
so what am I doing wrong as to complete the log and close the worksheet if
todays date is found in Column C a name shud be in column D and a 'Y' in E,
if not then the error respnse.
cheers

"Bob Phillips" wrote:

I just tested it, and it seems to work.

Are you sure you have those dates and a Y offset 2 columns?

BTW, I only coded what Norman suggested :-))

--

HTH

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


"Anthony" wrote in message
...
oops my mistake,
the code supplied by Bob and Norman works better but even after entering
all
the data into columns C,D and E I still get the propmt to enter data, the
workbook should now close,
any other ideas

"Anthony" wrote:

As always - Bob to the rescue !
Thanks

"Bob Phillips" wrote:

Dim TimeCheck As Date
Dim WS As Worksheet
Dim sRng As Range
Dim x As Object
Dim sDate As Date

TimeCheck = Format(Now(), "h:mm")
If TimeCheck "09:00" Then

sDate = DateSerial(Year(Now()), Month(Now()), Day(Now()))

Set WS = Sheets("sweep log")
With WS
Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp))
Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas,
SearchDirection:=xlNext)

If x Is Nothing Then
MsgBox "Todays Date not found" ' edit as needed
Cancel = True
Exit Sub
End If
If ActiveCell.Offset(0, 2) < "Y" Then
If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if
needed
MsgBox "verify sweep completed"
'this is old location
WS.Activate 'if "Y" is not found in same row as
todays _
'date then go to the empty cell below
x.Offset(0, 2).Select
Cancel = True
Exit Sub
End If 'this is new location
End If
End With
End If


--

HTH

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


"Anthony" wrote in message
...
Hi all,
I have had help putting this code together, but can't get it to work
properly. The code checks a worksheet named 'sweep log' Column C for
today's
date, if its not found then a msg box "todays date not found" is
displayed,
or if it is found then a check that data is also entered into the
adjoining
columns D and E.
Problem is that the checks are carried out and the correct msg boxes
are
shown but on acknowledment of these the worlsheet closes, instead of
allowing
the data to be entered.
The code has been placed within the Before Close event, and is shown
here...

Dim TimeCheck As Date
TimeCheck = Format(Now(), "h:mm")
If TimeCheck "09:00" Then

Dim WS As Worksheet
Dim sRng As Range
Dim x As Object
Dim sDate As Date

sDate = DateSerial(Year(Now()), Month(Now()), Day(Now()))


Set WS = Sheets("sweep log")
With WS
Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp))
Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas,
SearchDirection:=xlNext)

If x Is Nothing Then
MsgBox "Todays Date not found" ' edit as needed
SweepCheck = False
Exit Sub
End If
If ActiveCell.Offset(0, 2) < "Y" Then
If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed
MsgBox "verify sweep completed"
'this is old location
WS.Activate 'if "Y" is not found in same row as todays date then go
to the
empty cell below
x.Offset(0, 2).Select
Exit Sub
End If 'this is new location
End If
End With
End If

it must be something simple, but being a novice, I can't put my
finger on
it

any help apreciated
thanks