Msg Box does not close after clicking YES

[CA_User]

I have the following code in ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Did you PW protect this file?", vbInformation + vbYesNo + vbDefaultButton1, "Please Confirm before Closing") = vbNo Then
Cancel = True
Else
ActiveWorkbook.Close
End If
End Sub


When I click yes, the pop up message does not close. I have to click it once more and then it will exit. How can I make it close after clicking "yes" just once?

thank you.

 

[mikerickson]

You have walked yourself into an infinite loop.

"YES" leads to AcitveWorkbook.Close, which invokes the Before_Close event yet again.


Just omit the line
ActiveWorkbook.Close

 

[Rick Rothstein]

I have the following code in ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Did you PW protect this file?", vbInformation + vbYesNo + vbDefaultButton1, "Please Confirm before Closing") = vbNo Then
Cancel = True
Else
ActiveWorkbook.Close
End If
End Sub

When I click yes, the pop up message does not close. I have to click it once more and then it will exit. How can I make it close after clicking "yes" just once?

You do not need the ActiveWorkbook.Close statement... the whole reason the code is executing is because the workbook is in the process of being closed. I think that line makes it close again which kicks off your code again. Remove that line and your code should work as expected. By the way, you can reduce your code to this one-liner...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Cancel = (MsgBox("Did you PW protect this file?", vbInformation + vbYesNo + _
           vbDefaultButton1, "Please Confirm before Closing") = vbNo)
End Sub

 

[CA_User]

Thank you both very much!