Hi Anthony,

You need to cancel the closure of the workbook at some point.

Try inserting:

Cancel = True

after:

x.Offset(0, 2).Select

I have not otherwise looked at your code.

---
Regards,
Norman



"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