Thread: Workbook_BeforeClose

Hello,

I am trying to use the beforeclose event on a workbook. However, it appears
to be getting bypassed everytime I close the book as I am being asked the
standard "Do you want to save the changes to...". Here is what I have written
for it.

Private Sub workbook_beforeclose(Cancel As Boolean)

Cancel = False


If Sheets("Check").Range("Protected") = "Y" Then
If bBlockEvents Then Exit Sub
ThisWorkbook.Saved = True
bBlockEvents = True

Else
If Range("Prepare") > "" And Range("Checked") > "" And Not Range
("Protected") = "Y" Then
Message = MsgBox("Is this workbook finished with for today?",
vbYesNo + vbCritical)
If Message = vbNo Then
Call DeleteEVMenu 'Sub to delete custom menu
Else
Sheets("Check").Range("Protected") = "Y"
ProtectSheets 'Sub to protect certain sheets
Call DeleteEVMenu 'Sub to delete custom menu
ThisWorkbook.Save
End If
End If
End If

End Sub

Any help would be much appreciated

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1

Did you save it in the ThisWorbook code module?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Alan McQuaid via OfficeKB.com" <u22393@uwe> wrote in message
news:61b73b846b382@uwe...
> Hello,
>
> I am trying to use the beforeclose event on a workbook. However, it
appears
> to be getting bypassed everytime I close the book as I am being asked the
> standard "Do you want to save the changes to...". Here is what I have
written
> for it.
>
> Private Sub workbook_beforeclose(Cancel As Boolean)
>
> Cancel = False
>
>
> If Sheets("Check").Range("Protected") = "Y" Then
> If bBlockEvents Then Exit Sub
> ThisWorkbook.Saved = True
> bBlockEvents = True
>
> Else
> If Range("Prepare") > "" And Range("Checked") > "" And Not Range
> ("Protected") = "Y" Then
> Message = MsgBox("Is this workbook finished with for today?",
> vbYesNo + vbCritical)
> If Message = vbNo Then
> Call DeleteEVMenu 'Sub to delete custom menu
> Else
> Sheets("Check").Range("Protected") = "Y"
> ProtectSheets 'Sub to protect certain sheets
> Call DeleteEVMenu 'Sub to delete custom menu
> ThisWorkbook.Save
> End If
> End If
> End If
>
> End Sub
>
> Any help would be much appreciated
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200606/1

Bob Phillips wrote:
>Did you save it in the ThisWorbook code module?
>
>--
>
>HTH
>
>Bob Phillips
>
>(replace xxxx in the email address with gmail if mailing direct)
>
>> Hello,
>>
>[quoted text clipped - 31 lines]
>>
>> Any help would be much appreciated
Bob,

Apologies, this is included in the ThisWorkbook code module

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1

You should at least set Cancel to True.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Alan McQ via OfficeKB.com" <u22393@uwe> wrote in message
news:61b76a549afba@uwe...
> Bob Phillips wrote:
> >Did you save it in the ThisWorbook code module?
> >
> >--
> >
> >HTH
> >
> >Bob Phillips
> >
> >(replace xxxx in the email address with gmail if mailing direct)
> >
> >> Hello,
> >>
> >[quoted text clipped - 31 lines]
> >>
> >> Any help would be much appreciated
> Bob,
>
> Apologies, this is included in the ThisWorkbook code module
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200606/1