workbook with disabled close interferes with close workbook using command button

The Dutchman

Board Regular
Joined
Apr 10, 2008
Messages
72
I am attempting to force a workbook user to close the workbook using a command button; I've disabled the use of the X button in the top right hand corner of the applicationby using the following code:

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Cancel = True
 MsgBox "Please go to the Main Menu and use the 'Exit Promo System' button provided.", vbCritical, "Closing Promo System"

End Sub

The problem is that when the button is clicked to save and close the workbook, the beforeclose script is run again and I cannot exit.

How can I work my way around this? Do I need to incorporate a public variable?
 
Last edited:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
In you posted code why not just call the "Exit Promo" code after the Cancel?
 

The Dutchman

Board Regular
Joined
Apr 10, 2008
Messages
72
Good day Joe:

I know what you mean but I'm trying to get the user to realize that the normal close button for Excel is totally disabled and just have them use the one on the main menu.

Any ideas on how to accomplish this?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
At the top of your Exit Promo code add:

Application.EnableEvents = FALSE

Your code here!

At the end of your code add:

Application.EnableEvents = True

This will by-pass the Event code on a proper exit!
 

The Dutchman

Board Regular
Joined
Apr 10, 2008
Messages
72

ADVERTISEMENT

It works like a charm. Being relatively new to VBA I kind of scratched my head to the enableevents = false/true script I have come across.

This really hits home and is a great practical lesson.

Thank you very much Joe!

Ray Schrijvers
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
My comment about doing the exit for the user comes from being in the business a long time. No matter how logical our design is the user will think differently. So, we must look for the other ways a user will use our product. And, make our product work the way they want to use it.

Your trapping an improper exit is a step in the right direction.

The code below is for an Auto-Close MsgBox that uses Windows script object not an Excel VBA object, but it codes the same way for the most part. If you warn the user and then take them to the proper Exit Sheet or do the Exit for them after warrning them it is better than implying do it my way or not at all.


Sub TimedWarrning()
'Standard module code, like: Module1.
'Close message after time if no action!
Dim myTimedBox As Object
Dim boxTime&, myExpired&, myOK&, myQuestBox&

'Access timed message box.
Set myTimedBox = CreateObject("WScript.Shell")

boxTime = 5

'User took no Action!
myExpired = myTimedBox.Popup("You did not use the ""Exit Promo"" Button!" & vbLf & _
"Will now do so for you!" & vbLf & vbLf & _
"Do nothing and this will do it for you in 5 seconds.", _
boxTime, "Error: Improper Exit!", 0)

Call myExitCode
End Sub

Sub myExitCode()
MsgBox "Close Code Here!"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,912
Members
414,110
Latest member
docops

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