Problem with before close

[Raj]

Hi,

I am having a problem with the code below. The Msgbox appears twice
when I close the workbook Why?
Also, a Microsoft bug report is being generated everytime.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
rspresponse1 = MsgBox("Do you want to Finalise?", vbYesNo)
If rspresponse1 = vbYes Then Me.Close SaveChanges:=True
If rspresponse1 = vbNo Then Me.Close SaveChanges:=False
End Sub

Please help.

Thanks in advance.

Regards,
Raj

 

[paul.robinson]

Hi
You are invoking the close from within the close, which calls the
BeforeClose...hence the message box appears twice and you get an error
rather than an infinite loop. You need to suppress the BeforeClose
event being called again

Private Sub Workbook_BeforeClose(Cancel As Boolean)
rspresponse1 = MsgBox("Do you want to Finalise?", vbYesNo)
Application.EnableEvents = False
If rspresponse1 = vbYes Then Me.Close SaveChanges:=True
If rspresponse1 = vbNo Then Me.Close SaveChanges:=False
Application.EnableEvents = True
End Sub

You need to set it to true at the end as false lasts as long as Excel
is open, not to the end of the sub.
regards
Paul