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

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

Norman,
thanks for help, but by inserting Cancle=True didn't actualy do anything!
any other ideas ?
thanks

"Norman Jones" wrote:

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

Hi Anthony

Norman,
thanks for help, but by inserting Cancle=True didn't actualy do anything!
any other ideas ?
thanks

I may have mis read your code.

Try inserting the

Cancel = True

instruction after the line:

MsgBox "Todays Date not found" ' edit as needed

The workbook_BeforeClose procedure is invoked when an instruction to close
the workbook is issued. Including the instruction Cancel = True cancels the
original close instruction.

If, the code correctly reaches the MsgBox line, then the Cancel instruction
should prevent the file from closing.


---
Regards,
Norman



"Anthony" wrote in message
...
Norman,
thanks for help, but by inserting Cancle=True didn't actualy do anything!
any other ideas ?
thanks

"Norman Jones" wrote:

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

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

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

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

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

Hi Bob.

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

The temerity!!

---
Regards,
Norman



"Bob Phillips" wrote in message
...
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

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

Norman
This code, given by Bob and yourself........

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 ifneeded
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



.....this kinda works but as I said before if you have data entered into
columns C,D and E it still shows the "verify sweep complete" msg box, where
as it should exit
any ideas as to why this is not working?
thanks every so much
Anthony

"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

Hi Anthony,

My posted code is a little different. Did you try it,

In my test book, if columns C,D and E are correctly populated, the file
closes.


---
Regards,
Norman


"Anthony" wrote in message
...
Norman
This code, given by Bob and yourself........

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
ifneeded
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



....this kinda works but as I said before if you have data entered into
columns C,D and E it still shows the "verify sweep complete" msg box,
where
as it should exit
any ideas as to why this is not working?
thanks every so much
Anthony

"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

Hi Antony,

Just to add, you did not answer the questions I posed.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Anthony,

My posted code is a little different. Did you try it,

In my test book, if columns C,D and E are correctly populated, the file
closes.


---
Regards,
Norman