Solved: Prevent Workbook from closing

[JKwan]

I seem to be having difficulty preventing my workbook from closing. I am checking on a condition, if it is not met, I don't want the workbook to close until it is met. I thought that I am doing it correctly, however, I am not able to make it work. I've added code to the Workbook_BeforeClose event and it looks like this

CheckVessel
If bVesselsSent then
DeleteTooBars
Else
Cancel = True ' <---- I thought this would prevent the workbook from closing, however, this is not the case, my workwook still closes
End if


Am I misunderstanding it?

[mikerickson]

Your understanding of the use of the Cancel argument is correct.

It would be easier to comment on your implementation of that understanding if you posted the whole BeforeClose event.

How is the workbook being closed, by user action or by some other VB code. If by code, there might be something there that is causing problems.

Another guess in the dark is that perhaps CheckVessel has a branch that closes?

Have you stepped through the code to make sure that the Cancel = True line is executing?

[Kenneth Hobs]

In a Module:

Public tf As Boolean

Sub tfTrue()
tf = True
End Sub

Sub tfFalse()
tf = False
End Sub

In ThisWorkbook:
Private Sub Workbook_Open()
tf = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = tf
End Sub

[JKwan]

Your understanding of the use of the Cancel argument is correct.

It would be easier to comment on your implementation of that understanding if you posted the whole BeforeClose event.

How is the workbook being closed, by user action or by some other VB code. If by code, there might be something there that is causing problems.

Another guess in the dark is that perhaps CheckVessel has a branch that closes?

Have you stepped through the code to make sure that the
Cancel = True line is executing?

That is all the code that I have in the Workbook_BeforeClose event, nothing fancy. I stepped thru the code the cancel = true got executed, then my workbook is closed??? I hit the "x" on the top right corner of my workbook, the BeforeClose event kicked off, I made sure that the CheckVessel comes back false, branch to the false part of the IF statement, Cance = True got executed, workbook then closed.

The CheckVessel procedure only check to see if there is anything on the sheet, if there is, you need to "clean" it out, that is why I don't want the workbook closed.

[JKwan]

So, if I truely understand the Cancel = True part, then if I only have Cancel = True in the BeforeClose event, then my workbook should never close, am I correct? Well, I did that and my workbook is closed when I hit the "x" on the top right hand corner of my workbook, as I was stepping thru the code, it executed the code and closed my workbook. Does anyone know what may be happening.

[mikerickson]

what is bVesselsSent and how does it get its value?

[JKwan]

it is a global boolean variable, sets to true if the tab has more than 1 row otherwise sets to false.

[mikerickson]

I cannot understand why your workbook closes under those conditions.
Could you post your workbook after removing sensitive data?

[Kenneth Hobs]

Did you define the global variable in a Module only as I did?

Posting the workbook or a simplified one that demonstrates the problem is the best way to get help at this point.

[JKwan]

Here is a workbook that I whipped up. I put cancel = true in the BeforeClose event, then I hit the "x" on the workbook, the msg popped up, click ok, then the workbook closes.

[JKwan]

Did you define the global variable in a Module only as I did?

Posting the workbook or a simplified one that demonstrates the problem is the best way to get help at this point.

Kenneth:
Don't mean to ignore your help / idea, however, I was not able to overcome the simplest notion of one statement Cancel = True part in the BeforeClose event! I've posted what I am trying to do, maybe with it, people in the forum may be able to help me out.

Thanks.

[mikerickson]

The posted workbook will not close until I alter the BeforeClose event.
It works as expected.

[JKwan]

The posted workbook will not close until I alter the BeforeClose event.
It works as expected.

So, are you saying that the posted workbook does not close the workbook as is..... until you remove Cancel = True? Because my workbook just closes.

[Aflatoon]

Did you enable macros? If so, then I suspect something else has disabled events, so I suggest you restart Excel and try it again. (your workbook operates correctly for me in both 2010 and 2003.)

[JKwan]

Did you enable macros? If so, then I suspect something else has disabled events, so I suggest you restart Excel and try it again. (your workbook operates correctly for me in both 2010 and 2003.)

Thanks for your test. Yes, the macro is enabled. My msgbox pops up with the message, click ok, then my workbook closes. I power up every Monday morning, so the state my my computer cannot be any cleaner..... The good thing is that I guess the it is working the way it is, however, looks like both of my PCs are behaving "differently" and it is not working the way it intends to.

[Aflatoon]

If you run Excel in Safe Mode (open normally whilst holding down the Ctrl key), does the workbook behave the same way?

[JKwan]

Safe mode does it too.

I did discovered that looks like there are addins that is doing this strange thing. It is a Hummingbird addin (global for the company). If I completely delete it from my VBE, then Cancel = True is doing what it was meant to do. The bad, Hummingbird render it useless :-(. Thank you all for helping.

[Aflatoon]

In Safe Mode, no addins should be loaded (which was why I asked you to test it ;))
It sounds as though that addin is actually setting Cancel to False rather than leaving it as it is - very bad practice on the programmer's part.