VBA code for pop-up msgBox when closing an excel file

[JaanJ]

Hello,

I would like to create a pop-up message box when you try to close an excel file.
The idea is that the message is asking a question with a YES/NO answer. If you push YES, the file stays open and if you push NO then there will be a second message box and there if you push OK then the file will be closed.
I have found the following code on the internet, but it doesn´t seem to do what is needed.
I´m a total newbie when it comes to VBA.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim answer As String
Dim question As String
question = "ATTENTION! Your offer is not acceptable! You should lower the price!"
answer = MsgBox(question, vbYesNo)
If answer = vbNo Then
MsgBox "You should be more Customer focused! Go back?"
Cancel = True
Exit Sub
Else
ThisWorkbook.Save
End If
End Sub

Appreciate any support!
Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim question As String
   question = "ATTENTION! Your offer is not acceptable! You should lower the price!"
   If MsgBox(question, vbYesNo) = vbYes Then
      Cancel = True
      Exit Sub
   End If
   MsgBox "You should be more Customer focused! Go back?"
End Sub

 

[JaanJ]

Hi & welcome to MrExcel.
How about

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim question As String
   question = "ATTENTION! Your offer is not acceptable! You should lower the price!"
   If MsgBox(question, vbYesNo) = vbYes Then
      Cancel = True
      Exit Sub
   End If
   MsgBox "You should be more Customer focused! Go back?"
End Sub

Thank you!
Appreciate the quick reply!
When I copy your suggestion to VBA then the file is still closing if I hit the YES button.

 

[Fluff]

Did you put that code into the ThisWorkbook module?

 

[JaanJ]

Did you put that code into the ThisWorkbook module?

Hello,
Yes I did.

 

[Fluff]

Did you change the code at all?
Because if you click Yes on the first message box, it should cancel the close.

 

[JaanJ]

Did you change the code at all?
Because if you click Yes on the first message box, it should cancel the close.

Hi,
Now I have deleted all the codes in VBA. Saved the file. Opened it again. Made a copy-paste of your suggestion. Saved everything. And the result is still the same. When I press yes, the file is closing.

 

[Fluff]

As long as the code is in the Thisworkbook module of the file you are closing & it's saved as a macro enabled file, then that should not happen, so not sure what is happening.