workbook with disabled close interferes with close workbook using command button

[The Dutchman]

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:

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?

 

[Joe Was]

In you posted code why not just call the "Exit Promo" code after the Cancel?

 

[The Dutchman]

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]

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]

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]

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

 

[The Dutchman]

Thanks for your professional insight!