VB code help

[Anthony]

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 Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Anthony]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Anthony]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

[Anthony]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 Jones]

Hi Bob.

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

The temerity!!

---
Regards,
Norman



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > 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
>> > > >
>> > > >
>> > >
>> > >
>> > >
>
>

[Anthony]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > > >
>
>
>

[Anthony]

Norman,
can you help anymore with this ?
see my previous update,
many thanks

"Norman Jones" wrote:

> Hi Bob.
>
> BTW, I only coded what Norman suggested :-))
>
> The temerity!!
>
> ---
> Regards,
> Norman
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > 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 Jones]

Hi Anthony,

This may be getting nearer to what you want:

'==========>>
Private Sub Workbook_BeforeClose(Cancel As Boolean)

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 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 = False
Exit Sub
End If 'this is new location
End If
End With
End If
End Sub
'<<==========

However several things are unclear to me.

If today's date is found, must both the corresponding D an F cells be
populated?

If the D / F cells are not populated, which cell is to be selected. The
changes in the above code relect my (current) best guesses.


---
Regards,
Norman



"Anthony" <[email protected]> wrote in message
news:[email protected]...
> Norman,
> can you help anymore with this ?
> see my previous update,
> many thanks
>
> "Norman Jones" wrote:
>
>> Hi Bob.
>>
>> BTW, I only coded what Norman suggested :-))
>>
>> The temerity!!
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> > > news:[email protected]...
>> >> > > > 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
>> >> > > >
>> >> > > >
>> >> > >
>> >> > >
>> >> > >
>> >
>> >
>>
>>
>>

[Anthony]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 Jones]

Hi Antony,

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

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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]

Norman,
Ok I placed your code into my Beforeclose event so removing the Private Sub
Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the
end. But still the workbook closes after showing the "varify sweep complete"
msg box.
Can you tell me exactly what data you placed in cells C7,D7 and E7 to make
it work??
Also sorry I missed you other questions, didn't notice them at the end, so
in answer to them....

If today's date is found, must both the corresponding D an E cells be
populated?
Yes

If the D / E cells are not populated, which cell is to be selected. The
changes in the above code relect my (current) best guesses.
If the date is found but no other data then either column D or E can be
selected

Hope this helps, now can you solve it for me
thanks again
Anthony




"Norman Jones" wrote:

> Hi Anthony,
>
> This may be getting nearer to what you want:
>
> '==========>>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> 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 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 = False
> Exit Sub
> End If 'this is new location
> End If
> End With
> End If
> End Sub
> '<<==========
>
> However several things are unclear to me.
>
> If today's date is found, must both the corresponding D an F cells be
> populated?
>
> If the D / F cells are not populated, which cell is to be selected. The
> changes in the above code relect my (current) best guesses.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Anthony" <[email protected]> wrote in message
> news:[email protected]...
> > Norman,
> > can you help anymore with this ?
> > see my previous update,
> > many thanks
> >
> > "Norman Jones" wrote:
> >
> >> Hi Bob.
> >>
> >> BTW, I only coded what Norman suggested :-))
> >>
> >> The temerity!!
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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" <[email protected]> wrote in message
> >> >> > > news:[email protected]...
> >> >> > > > 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 Phillips]

Anthony,

I found it failed if the date cell contained a formula date, so I change dit
to this and it works, at least for me

Private Sub Workbook_BeforeClose(Cancel As Boolean)

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 End If 'this is new
location
End If
End With
End If
End Sub


--

HTH

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


"Anthony" <[email protected]> wrote in message
news:[email protected]...
> Norman,
> Ok I placed your code into my Beforeclose event so removing the Private
Sub
> Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the
> end. But still the workbook closes after showing the "varify sweep
complete"
> msg box.
> Can you tell me exactly what data you placed in cells C7,D7 and E7 to make
> it work??
> Also sorry I missed you other questions, didn't notice them at the end, so
> in answer to them....
>
> If today's date is found, must both the corresponding D an E cells be
> populated?
> Yes
>
> If the D / E cells are not populated, which cell is to be selected. The
> changes in the above code relect my (current) best guesses.
> If the date is found but no other data then either column D or E can be
> selected
>
> Hope this helps, now can you solve it for me
> thanks again
> Anthony
>
>
>
>
> "Norman Jones" wrote:
>
> > Hi Anthony,
> >
> > This may be getting nearer to what you want:
> >
> > '==========>>
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >
> > 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 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 = False
> > Exit Sub
> > End If 'this is new location
> > End If
> > End With
> > End If
> > End Sub
> > '<<==========
> >
> > However several things are unclear to me.
> >
> > If today's date is found, must both the corresponding D an F cells be
> > populated?
> >
> > If the D / F cells are not populated, which cell is to be selected. The
> > changes in the above code relect my (current) best guesses.
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Anthony" <[email protected]> wrote in message
> > news:[email protected]...
> > > Norman,
> > > can you help anymore with this ?
> > > see my previous update,
> > > many thanks
> > >
> > > "Norman Jones" wrote:
> > >
> > >> Hi Bob.
> > >>
> > >> BTW, I only coded what Norman suggested :-))
> > >>
> > >> The temerity!!
> > >>
> > >> ---
> > >> Regards,
> > >> Norman
> > >>
> > >>
> > >>
> > >> "Bob Phillips" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >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" <[email protected]> wrote in message
> > >> > news:[email protected]...
> > >> >> 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" <[email protected]> wrote in message
> > >> >> > > news:[email protected]...
> > >> >> > > > 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 Jones]

Hi Anthony,

> Can you tell me exactly what data you placed in cells C7,D7 and
> E7 to make it work??

For test purposes, I made the very simplest of worksheets: I entered a
sequence of dates in cells C6:C21; the date in cell C11 being today's date.
In D11 I entered a name and in E11 I entered Y.

With this arrangement the workbook closes; if I then delete the name entry,
I get the msgbox and the workbook remains open.

In this simple test, no other cells were populated.

You did notice that, in the second instance, I set Cancel = True


Regards,
Norman


"Anthony" <[email protected]> wrote in message
news:[email protected]...
> Norman,
> Ok I placed your code into my Beforeclose event so removing the Private
> Sub
> Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the
> end. But still the workbook closes after showing the "varify sweep
> complete"
> msg box.
> Can you tell me exactly what data you placed in cells C7,D7 and E7 to make
> it work??
> Also sorry I missed you other questions, didn't notice them at the end, so
> in answer to them....
>
> If today's date is found, must both the corresponding D an E cells be
> populated?
> Yes
>
> If the D / E cells are not populated, which cell is to be selected. The
> changes in the above code relect my (current) best guesses.
> If the date is found but no other data then either column D or E can be
> selected
>
> Hope this helps, now can you solve it for me
> thanks again
> Anthony
>
>
>
>
> "Norman Jones" wrote:
>
>> Hi Anthony,
>>
>> This may be getting nearer to what you want:
>>
>> '==========>>
>> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>>
>> 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 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 = False
>> Exit Sub
>> End If 'this is new location
>> End If
>> End With
>> End If
>> End Sub
>> '<<==========
>>
>> However several things are unclear to me.
>>
>> If today's date is found, must both the corresponding D an F cells be
>> populated?
>>
>> If the D / F cells are not populated, which cell is to be selected. The
>> changes in the above code relect my (current) best guesses.
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Anthony" <[email protected]> wrote in message
>> news:[email protected]...
>> > Norman,
>> > can you help anymore with this ?
>> > see my previous update,
>> > many thanks
>> >
>> > "Norman Jones" wrote:
>> >
>> >> Hi Bob.
>> >>
>> >> BTW, I only coded what Norman suggested :-))
>> >>
>> >> The temerity!!
>> >>
>> >> ---
>> >> Regards,
>> >> Norman
>> >>
>> >>
>> >>
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> 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" <[email protected]> wrote in message
>> >> >> > > news:[email protected]...
>> >> >> > > > 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
>> >> >> > > >
>> >> >> > > >
>> >> >> > >
>> >> >> > >
>> >> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[Anthony]

Bob,
see my last post to Norman, his code almost worked, the one you just posted,
again closes without alowing user to input data -
helllpppp !

"Bob Phillips" wrote:

> Anthony,
>
> I found it failed if the date cell contained a formula date, so I change dit
> to this and it works, at least for me
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> 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 End If 'this is new
> location
> End If
> End With
> End If
> End Sub
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Anthony" <[email protected]> wrote in message
> news:[email protected]...
> > Norman,
> > Ok I placed your code into my Beforeclose event so removing the Private
> Sub
> > Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the
> > end. But still the workbook closes after showing the "varify sweep
> complete"
> > msg box.
> > Can you tell me exactly what data you placed in cells C7,D7 and E7 to make
> > it work??
> > Also sorry I missed you other questions, didn't notice them at the end, so
> > in answer to them....
> >
> > If today's date is found, must both the corresponding D an E cells be
> > populated?
> > Yes
> >
> > If the D / E cells are not populated, which cell is to be selected. The
> > changes in the above code relect my (current) best guesses.
> > If the date is found but no other data then either column D or E can be
> > selected
> >
> > Hope this helps, now can you solve it for me
> > thanks again
> > Anthony
> >
> >
> >
> >
> > "Norman Jones" wrote:
> >
> > > Hi Anthony,
> > >
> > > This may be getting nearer to what you want:
> > >
> > > '==========>>
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > >
> > > 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 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 = False
> > > Exit Sub
> > > End If 'this is new location
> > > End If
> > > End With
> > > End If
> > > End Sub
> > > '<<==========
> > >
> > > However several things are unclear to me.
> > >
> > > If today's date is found, must both the corresponding D an F cells be
> > > populated?
> > >
> > > If the D / F cells are not populated, which cell is to be selected. The
> > > changes in the above code relect my (current) best guesses.
> > >
> > >
> > > ---
> > > Regards,
> > > Norman
> > >
> > >
> > >
> > > "Anthony" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Norman,
> > > > can you help anymore with this ?
> > > > see my previous update,
> > > > many thanks
> > > >
> > > > "Norman Jones" wrote:
> > > >
> > > >> Hi Bob.
> > > >>
> > > >> BTW, I only coded what Norman suggested :-))
> > > >>
> > > >> The temerity!!
> > > >>
> > > >> ---
> > > >> Regards,
> > > >> Norman
> > > >>
> > > >>
> > > >>
> > > >> "Bob Phillips" <[email protected]> wrote in message
> > > >> news:[email protected]...
> > > >> >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" <[email protected]> wrote in message
> > > >> > news:[email protected]...
> > > >> >> 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" <[email protected]> wrote in message
> > > >> >> > > news:[email protected]...
> > > >> >> > > > 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

[Norman Jones]

Hi Anthony,

Bob's code worked for me providing I added:

Cancel = True

after the line:

>> x.Offset(1).Select

---
Regards,
Norman


"Anthony" <[email protected]> wrote in message
news:[email protected]...
> Bob,
> see my last post to Norman, his code almost worked, the one you just
> posted,
> again closes without alowing user to input data -
> helllpppp !
>
> "Bob Phillips" wrote:
>
>> Anthony,
>>
>> I found it failed if the date cell contained a formula date, so I change
>> dit
>> to this and it works, at least for me
>>
>> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>>
>> 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 End If 'this is new
>> location
>> End If
>> End With
>> End If
>> End Sub
>>
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "Anthony" <[email protected]> wrote in message
>> news:[email protected]...
>> > Norman,
>> > Ok I placed your code into my Beforeclose event so removing the Private
>> Sub
>> > Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at
>> > the
>> > end. But still the workbook closes after showing the "varify sweep
>> complete"
>> > msg box.
>> > Can you tell me exactly what data you placed in cells C7,D7 and E7 to
>> > make
>> > it work??
>> > Also sorry I missed you other questions, didn't notice them at the end,
>> > so
>> > in answer to them....
>> >
>> > If today's date is found, must both the corresponding D an E cells be
>> > populated?
>> > Yes
>> >
>> > If the D / E cells are not populated, which cell is to be selected. The
>> > changes in the above code relect my (current) best guesses.
>> > If the date is found but no other data then either column D or E can be
>> > selected
>> >
>> > Hope this helps, now can you solve it for me
>> > thanks again
>> > Anthony
>> >
>> >
>> >
>> >
>> > "Norman Jones" wrote:
>> >
>> > > Hi Anthony,
>> > >
>> > > This may be getting nearer to what you want:
>> > >
>> > > '==========>>
>> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> > >
>> > > 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 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 = False
>> > > Exit Sub
>> > > End If 'this is new location
>> > > End If
>> > > End With
>> > > End If
>> > > End Sub
>> > > '<<==========
>> > >
>> > > However several things are unclear to me.
>> > >
>> > > If today's date is found, must both the corresponding D an F cells be
>> > > populated?
>> > >
>> > > If the D / F cells are not populated, which cell is to be selected.
>> > > The
>> > > changes in the above code relect my (current) best guesses.
>> > >
>> > >
>> > > ---
>> > > Regards,
>> > > Norman
>> > >
>> > >
>> > >
>> > > "Anthony" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > Norman,
>> > > > can you help anymore with this ?
>> > > > see my previous update,
>> > > > many thanks
>> > > >
>> > > > "Norman Jones" wrote:
>> > > >
>> > > >> Hi Bob.
>> > > >>
>> > > >> BTW, I only coded what Norman suggested :-))
>> > > >>
>> > > >> The temerity!!
>> > > >>
>> > > >> ---
>> > > >> Regards,
>> > > >> Norman
>> > > >>
>> > > >>
>> > > >>
>> > > >> "Bob Phillips" <[email protected]> wrote in
>> > > >> message
>> > > >> news:[email protected]...
>> > > >> >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" <[email protected]> wrote in message
>> > > >> > news:[email protected]...
>> > > >> >> 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" <[email protected]> wrote in
>> > > >> >> > > message
>> > > >> >> > > news:[email protected]...
>> > > >> >> > > > 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

[Anthony]

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


"Norman Jones" wrote:

> Hi Anthony,
>
> > Can you tell me exactly what data you placed in cells C7,D7 and
> > E7 to make it work??
>
> For test purposes, I made the very simplest of worksheets: I entered a
> sequence of dates in cells C6:C21; the date in cell C11 being today's date.
> In D11 I entered a name and in E11 I entered Y.
>
> With this arrangement the workbook closes; if I then delete the name entry,
> I get the msgbox and the workbook remains open.
>
> In this simple test, no other cells were populated.
>
> You did notice that, in the second instance, I set Cancel = True
>
>
> Regards,
> Norman
>
>
> "Anthony" <[email protected]> wrote in message
> news:[email protected]...
> > Norman,
> > Ok I placed your code into my Beforeclose event so removing the Private
> > Sub
> > Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the
> > end. But still the workbook closes after showing the "varify sweep
> > complete"
> > msg box.
> > Can you tell me exactly what data you placed in cells C7,D7 and E7 to make
> > it work??
> > Also sorry I missed you other questions, didn't notice them at the end, so
> > in answer to them....
> >
> > If today's date is found, must both the corresponding D an E cells be
> > populated?
> > Yes
> >
> > If the D / E cells are not populated, which cell is to be selected. The
> > changes in the above code relect my (current) best guesses.
> > If the date is found but no other data then either column D or E can be
> > selected
> >
> > Hope this helps, now can you solve it for me
> > thanks again
> > Anthony
> >
> >
> >
> >
> > "Norman Jones" wrote:
> >
> >> Hi Anthony,
> >>
> >> This may be getting nearer to what you want:
> >>
> >> '==========>>
> >> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >>
> >> 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 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 = False
> >> Exit Sub
> >> End If 'this is new location
> >> End If
> >> End With
> >> End If
> >> End Sub
> >> '<<==========
> >>
> >> However several things are unclear to me.
> >>
> >> If today's date is found, must both the corresponding D an F cells be
> >> populated?
> >>
> >> If the D / F cells are not populated, which cell is to be selected. The
> >> changes in the above code relect my (current) best guesses.
> >>
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Anthony" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Norman,
> >> > can you help anymore with this ?
> >> > see my previous update,
> >> > many thanks
> >> >
> >> > "Norman Jones" wrote:
> >> >
> >> >> Hi Bob.
> >> >>
> >> >> BTW, I only coded what Norman suggested :-))
> >> >>
> >> >> The temerity!!
> >> >>
> >> >> ---
> >> >> Regards,
> >> >> Norman
> >> >>
> >> >>
> >> >>
> >> >> "Bob Phillips" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >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" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> 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" <[email protected]> wrote in message
> >> >> >> > > news:[email protected]...
> >> >> >> > > > 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

[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)


"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi Anthony,
>
> Bob's code worked for me providing I added:
>
> Cancel = True
>
> after the line:
>
> >> x.Offset(1).Select
>
> ---
> Regards,
> Norman
>
>
> "Anthony" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> > see my last post to Norman, his code almost worked, the one you just
> > posted,
> > again closes without alowing user to input data -
> > helllpppp !
> >
> > "Bob Phillips" wrote:
> >
> >> Anthony,
> >>
> >> I found it failed if the date cell contained a formula date, so I
change
> >> dit
> >> to this and it works, at least for me
> >>
> >> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >>
> >> 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 End If 'this is
new
> >> location
> >> End If
> >> End With
> >> End If
> >> End Sub
> >>
> >>
> >> --
> >>
> >> HTH
> >>
> >> RP
> >> (remove nothere from the email address if mailing direct)
> >>
> >>
> >> "Anthony" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Norman,
> >> > Ok I placed your code into my Beforeclose event so removing the
Private
> >> Sub
> >> > Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at
> >> > the
> >> > end. But still the workbook closes after showing the "varify sweep
> >> complete"
> >> > msg box.
> >> > Can you tell me exactly what data you placed in cells C7,D7 and E7 to
> >> > make
> >> > it work??
> >> > Also sorry I missed you other questions, didn't notice them at the
end,
> >> > so
> >> > in answer to them....
> >> >
> >> > If today's date is found, must both the corresponding D an E cells be
> >> > populated?
> >> > Yes
> >> >
> >> > If the D / E cells are not populated, which cell is to be selected.
The
> >> > changes in the above code relect my (current) best guesses.
> >> > If the date is found but no other data then either column D or E can
be
> >> > selected
> >> >
> >> > Hope this helps, now can you solve it for me
> >> > thanks again
> >> > Anthony
> >> >
> >> >
> >> >
> >> >
> >> > "Norman Jones" wrote:
> >> >
> >> > > Hi Anthony,
> >> > >
> >> > > This may be getting nearer to what you want:
> >> > >
> >> > > '==========>>
> >> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >> > >
> >> > > 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 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 = False
> >> > > Exit Sub
> >> > > End If 'this is new location
> >> > > End If
> >> > > End With
> >> > > End If
> >> > > End Sub
> >> > > '<<==========
> >> > >
> >> > > However several things are unclear to me.
> >> > >
> >> > > If today's date is found, must both the corresponding D an F cells
be
> >> > > populated?
> >> > >
> >> > > If the D / F cells are not populated, which cell is to be selected.
> >> > > The
> >> > > changes in the above code relect my (current) best guesses.
> >> > >
> >> > >
> >> > > ---
> >> > > Regards,
> >> > > Norman
> >> > >
> >> > >
> >> > >
> >> > > "Anthony" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > Norman,
> >> > > > can you help anymore with this ?
> >> > > > see my previous update,
> >> > > > many thanks
> >> > > >
> >> > > > "Norman Jones" wrote:
> >> > > >
> >> > > >> Hi Bob.
> >> > > >>
> >> > > >> BTW, I only coded what Norman suggested :-))
> >> > > >>
> >> > > >> The temerity!!
> >> > > >>
> >> > > >> ---
> >> > > >> Regards,
> >> > > >> Norman
> >> > > >>
> >> > > >>
> >> > > >>
> >> > > >> "Bob Phillips" <[email protected]> wrote in
> >> > > >> message
> >> > > >> news:[email protected]...
> >> > > >> >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" <[email protected]> wrote in message
> >> > > >> > news:[email protected]...
> >> > > >> >> 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" <[email protected]> wrote in
> >> > > >> >> > > message
> >> > > >> >> > >
news:[email protected]...
> >> > > >> >> > > > 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
>
>

[Bob Phillips]

How about mailing me a workbook?

--

HTH

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


"Anthony" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Norman Jones" wrote:
>
> > Hi Anthony,
> >
> > > Can you tell me exactly what data you placed in cells C7,D7 and
> > > E7 to make it work??
> >
> > For test purposes, I made the very simplest of worksheets: I entered a
> > sequence of dates in cells C6:C21; the date in cell C11 being today's
date.
> > In D11 I entered a name and in E11 I entered Y.
> >
> > With this arrangement the workbook closes; if I then delete the name
entry,
> > I get the msgbox and the workbook remains open.
> >
> > In this simple test, no other cells were populated.
> >
> > You did notice that, in the second instance, I set Cancel = True
> >
> >
> > Regards,
> > Norman
> >
> >
> > "Anthony" <[email protected]> wrote in message
> > news:[email protected]...
> > > Norman,
> > > Ok I placed your code into my Beforeclose event so removing the
Private
> > > Sub
> > > Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at
the
> > > end. But still the workbook closes after showing the "varify sweep
> > > complete"
> > > msg box.
> > > Can you tell me exactly what data you placed in cells C7,D7 and E7 to
make
> > > it work??
> > > Also sorry I missed you other questions, didn't notice them at the
end, so
> > > in answer to them....
> > >
> > > If today's date is found, must both the corresponding D an E cells be
> > > populated?
> > > Yes
> > >
> > > If the D / E cells are not populated, which cell is to be selected.
The
> > > changes in the above code relect my (current) best guesses.
> > > If the date is found but no other data then either column D or E can
be
> > > selected
> > >
> > > Hope this helps, now can you solve it for me
> > > thanks again
> > > Anthony
> > >
> > >
> > >
> > >
> > > "Norman Jones" wrote:
> > >
> > >> Hi Anthony,
> > >>
> > >> This may be getting nearer to what you want:
> > >>
> > >> '==========>>
> > >> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > >>
> > >> 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 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 = False
> > >> Exit Sub
> > >> End If 'this is new location
> > >> End If
> > >> End With
> > >> End If
> > >> End Sub
> > >> '<<==========
> > >>
> > >> However several things are unclear to me.
> > >>
> > >> If today's date is found, must both the corresponding D an F cells be
> > >> populated?
> > >>
> > >> If the D / F cells are not populated, which cell is to be selected.
The
> > >> changes in the above code relect my (current) best guesses.
> > >>
> > >>
> > >> ---
> > >> Regards,
> > >> Norman
> > >>
> > >>
> > >>
> > >> "Anthony" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > Norman,
> > >> > can you help anymore with this ?
> > >> > see my previous update,
> > >> > many thanks
> > >> >
> > >> > "Norman Jones" wrote:
> > >> >
> > >> >> Hi Bob.
> > >> >>
> > >> >> BTW, I only coded what Norman suggested :-))
> > >> >>
> > >> >> The temerity!!
> > >> >>
> > >> >> ---
> > >> >> Regards,
> > >> >> Norman
> > >> >>
> > >> >>
> > >> >>
> > >> >> "Bob Phillips" <[email protected]> wrote in
message
> > >> >> news:[email protected]...
> > >> >> >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" <[email protected]> wrote in message
> > >> >> > news:[email protected]...
> > >> >> >> 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" <[email protected]> wrote in
message
> > >> >> >> > > news:[email protected]...
> > >> >> >> > > > 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

[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.


---
Regards,
Norman


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
>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)
>

[Anthony]

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" wrote:

> How about mailing me a workbook?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Anthony" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > "Norman Jones" wrote:
> >
> > > Hi Anthony,
> > >
> > > > Can you tell me exactly what data you placed in cells C7,D7 and
> > > > E7 to make it work??
> > >
> > > For test purposes, I made the very simplest of worksheets: I entered a
> > > sequence of dates in cells C6:C21; the date in cell C11 being today's
> date.
> > > In D11 I entered a name and in E11 I entered Y.
> > >
> > > With this arrangement the workbook closes; if I then delete the name
> entry,
> > > I get the msgbox and the workbook remains open.
> > >
> > > In this simple test, no other cells were populated.
> > >
> > > You did notice that, in the second instance, I set Cancel = True
> > >
> > >
> > > Regards,
> > > Norman
> > >
> > >
> > > "Anthony" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Norman,
> > > > Ok I placed your code into my Beforeclose event so removing the
> Private
> > > > Sub
> > > > Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at
> the
> > > > end. But still the workbook closes after showing the "varify sweep
> > > > complete"
> > > > msg box.
> > > > Can you tell me exactly what data you placed in cells C7,D7 and E7 to
> make
> > > > it work??
> > > > Also sorry I missed you other questions, didn't notice them at the
> end, so
> > > > in answer to them....
> > > >
> > > > If today's date is found, must both the corresponding D an E cells be
> > > > populated?
> > > > Yes
> > > >
> > > > If the D / E cells are not populated, which cell is to be selected.
> The
> > > > changes in the above code relect my (current) best guesses.
> > > > If the date is found but no other data then either column D or E can
> be
> > > > selected
> > > >
> > > > Hope this helps, now can you solve it for me
> > > > thanks again
> > > > Anthony
> > > >
> > > >
> > > >
> > > >
> > > > "Norman Jones" wrote:
> > > >
> > > >> Hi Anthony,
> > > >>
> > > >> This may be getting nearer to what you want:
> > > >>
> > > >> '==========>>
> > > >> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > >>
> > > >> 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 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 = False
> > > >> Exit Sub
> > > >> End If 'this is new location
> > > >> End If
> > > >> End With
> > > >> End If
> > > >> End Sub
> > > >> '<<==========
> > > >>
> > > >> However several things are unclear to me.
> > > >>
> > > >> If today's date is found, must both the corresponding D an F cells be
> > > >> populated?
> > > >>
> > > >> If the D / F cells are not populated, which cell is to be selected.
> The
> > > >> changes in the above code relect my (current) best guesses.
> > > >>
> > > >>
> > > >> ---
> > > >> Regards,
> > > >> Norman
> > > >>
> > > >>
> > > >>
> > > >> "Anthony" <[email protected]> wrote in message
> > > >> news:[email protected]...
> > > >> > Norman,
> > > >> > can you help anymore with this ?
> > > >> > see my previous update,
> > > >> > many thanks
> > > >> >
> > > >> > "Norman Jones" wrote:
> > > >> >
> > > >> >> Hi Bob.
> > > >> >>
> > > >> >> BTW, I only coded what Norman suggested :-))
> > > >> >>
> > > >> >> The temerity!!
> > > >> >>
> > > >> >> ---
> > > >> >> Regards,
> > > >> >> Norman
> > > >> >>
> > > >> >>
> > > >> >>
> > > >> >> "Bob Phillips" <[email protected]> wrote in
> message
> > > >> >> news:[email protected]...
> > > >> >> >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" <[email protected]> wrote in message
> > > >> >> > news:[email protected]...
> > > >> >> >> 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
> > > >> >> >> > >

[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)


"Anthony" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > How about mailing me a workbook?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Anthony" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> > > "Norman Jones" wrote:
> > >
> > > > Hi Anthony,
> > > >
> > > > > Can you tell me exactly what data you placed in cells C7,D7 and
> > > > > E7 to make it work??
> > > >
> > > > For test purposes, I made the very simplest of worksheets: I entered
a
> > > > sequence of dates in cells C6:C21; the date in cell C11 being
today's
> > date.
> > > > In D11 I entered a name and in E11 I entered Y.
> > > >
> > > > With this arrangement the workbook closes; if I then delete the name
> > entry,
> > > > I get the msgbox and the workbook remains open.
> > > >
> > > > In this simple test, no other cells were populated.
> > > >
> > > > You did notice that, in the second instance, I set Cancel = True
> > > >
> > > >
> > > > Regards,
> > > > Norman
> > > >
> > > >
> > > > "Anthony" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Norman,
> > > > > Ok I placed your code into my Beforeclose event so removing the
> > Private
> > > > > Sub
> > > > > Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub
at
> > the
> > > > > end. But still the workbook closes after showing the "varify sweep
> > > > > complete"
> > > > > msg box.
> > > > > Can you tell me exactly what data you placed in cells C7,D7 and E7
to
> > make
> > > > > it work??
> > > > > Also sorry I missed you other questions, didn't notice them at the
> > end, so
> > > > > in answer to them....
> > > > >
> > > > > If today's date is found, must both the corresponding D an E cells
be
> > > > > populated?
> > > > > Yes
> > > > >
> > > > > If the D / E cells are not populated, which cell is to be
selected.
> > The
> > > > > changes in the above code relect my (current) best guesses.
> > > > > If the date is found but no other data then either column D or E
can
> > be
> > > > > selected
> > > > >
> > > > > Hope this helps, now can you solve it for me
> > > > > thanks again
> > > > > Anthony
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Norman Jones" wrote:
> > > > >
> > > > >> Hi Anthony,
> > > > >>
> > > > >> This may be getting nearer to what you want:
> > > > >>
> > > > >> '==========>>
> > > > >> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > >>
> > > > >> 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 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 = False
> > > > >> Exit Sub
> > > > >> End If 'this is new location
> > > > >> End If
> > > > >> End With
> > > > >> End If
> > > > >> End Sub
> > > > >> '<<==========
> > > > >>
> > > > >> However several things are unclear to me.
> > > > >>
> > > > >> If today's date is found, must both the corresponding D an F
cells be
> > > > >> populated?
> > > > >>
> > > > >> If the D / F cells are not populated, which cell is to be
selected.
> > The
> > > > >> changes in the above code relect my (current) best guesses.
> > > > >>
> > > > >>
> > > > >> ---
> > > > >> Regards,
> > > > >> Norman
> > > > >>
> > > > >>
> > > > >>
> > > > >> "Anthony" <[email protected]> wrote in message
> > > > >> news:[email protected]...
> > > > >> > Norman,
> > > > >> > can you help anymore with this ?
> > > > >> > see my previous update,
> > > > >> > many thanks
> > > > >> >
> > > > >> > "Norman Jones" wrote:
> > > > >> >
> > > > >> >> Hi Bob.
> > > > >> >>
> > > > >> >> BTW, I only coded what Norman suggested :-))
> > > > >> >>
> > > > >> >> The temerity!!
> > > > >> >>
> > > > >> >> ---
> > > > >> >> Regards,
> > > > >> >> Norman
> > > > >> >>
> > > > >> >>
> > > > >> >>
> > > > >> >> "Bob Phillips" <[email protected]> wrote in
> > message
> > > > >> >> news:[email protected]...
> > > > >> >> >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" <[email protected]> wrote in
message
> > > > >> >> > news:[email protected]...
> > > > >> >> >> 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
> > > > >> >> >> > >