VB code help

[Guest]

Norman/Bob,
Guys thanks so much for your patience with this - now its driving me nuts.
You say it works for you, I copy and execute the same as you do and mine
does'nt

I'll expalin exactly what I am doing here, maybe you can see my error.

This is the exact code I have placed into my before close event......

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(Rows.Count, 3).End(xlUp))
Set x = sRng.Find(What:=sDate, LookIn:=xlValues, _
SearchDirection:=xlNext)

If x Is Nothing Then
MsgBox "Todays Date not found" ' edit as needed
Cancel = True
ElseIf x.Offset(0, 2) <> "Y" Then
If x.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(1).Select
Cancel = True

End If 'this is new Location
End If
End With
End If


I have the column C formatted as Custom dd mmmm yyyy

so, first test, nothing populated in any cell, I close the workbook and the
msg box "todays date not found", which is correct, I then click on OK and the
log closes !!!

sorry guys but can you help further
many thanks

 

[Bob Phillips]

I thought he wanted to close in that circumstance? Your original code had
Cancel = False, so I just removed it.

--

HTH

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

 

[Bob Phillips]

How about mailing me a workbook?

--

HTH

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

 

[Norman Jones]

Hi Bob,

Given Anthony's experience, I no longer know.

Doubtless, however, your kind offer to review Anthony's book will clarify
matters.

 

[Guest]

Bob,
the actual workbook is almost 7mb ! so I won't send that but I will send a
copy of the worksheet that the code is to work in, is that ok?

 

[Bob Phillips]

Yeah, that is fine. Test it first to make sure it behaves as you are saying.

--

HTH

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