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

JaanJ

New Member
Joined
Aug 25, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,831
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
Aug 25, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,831
Office Version
  1. 365
Platform
  1. Windows
Did you put that code into the ThisWorkbook module?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,831
Office Version
  1. 365
Platform
  1. Windows
Did you change the code at all?
Because if you click Yes on the first message box, it should cancel the close.
 

JaanJ

New Member
Joined
Aug 25, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,831
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,327
Messages
5,595,526
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top