Static vars aren't static enough

[Jerry Natowitz]

I'm trying to write Workbook_Activate and Workbook_Deactivate procedures
that will check for particular data and msgbox a warning.

First problem is if there is another Workbook open, the Activate or
Deactivate routine is called whenever focus changes between workbooks.
I haven't tried Workbook_Open, but since there isn't a Workbook_Close,
that would half solve my problem at best.

I've tried using static vars in the routines, but they seem to get
reinitialized to zero whenever the Workbook is activated.

Any suggestions?

 

[Jerry Natowitz]

Since writing this I found out about App_WorkbookBeforeClose, and I
thought all my troubles were over. Ha! Here is a snippet of code
(Excel 2004):

Public WithEvents App As Application
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
....
ret = MsgBox("One or more staff entries are not 100% funded!
Do you want to go back to edit?", vbYesNo)
If ret = vbYes Then
Cancel = True
End If
....

If I am just closing the sheet, this works fine. If I am exiting Excel,
and select Yes, Excel crashes.

 

[JE McGimpsey]

Since writing this I found out about App_WorkbookBeforeClose, and I
thought all my troubles were over. Ha! Here is a snippet of code
(Excel 2004):

Public WithEvents App As Application
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
...
ret = MsgBox("One or more staff entries are not 100% funded!
Do you want to go back to edit?", vbYesNo)
If ret = vbYes Then
Cancel = True
End If
...

If I am just closing the sheet, this works fine. If I am exiting Excel,
and select Yes, Excel crashes.

I can't reproduce this. I implemented the above (as class module
"Class1") by putting this in a regular code module:

Public a As Class1

and this in the ThisWorkbook module:

Private Sub Workbook_Open()
Set a = New Class1
End Sub


I could then close either the workbook or XL, and get the message box.

Note - The message box fired for each workbook that closed, naturally -
do you have other event macros that might be interfering?

 

[Jerry Natowitz]

It isn't the message box firing off that is the problem. If I answer
yes to the message box when I am closing the workbook, all is fine. If
I answer yes when I am quitting Excel, Excel crashes.

BTW: When I tried using Public vars, the msgbox didn't happen.

 

[JE McGimpsey]

It isn't the message box firing off that is the problem. If I answer
yes to the message box when I am closing the workbook, all is fine. If
I answer yes when I am quitting Excel, Excel crashes.

that's why I said that I could "close either the workbook or XL". I
don't get a crash with either. Did you check to see if you have other
workbooks open (such as the Personal Macro Workbook) that also have
BeforeClose code?

BTW: When I tried using Public vars, the msgbox didn't happen.

Not sure what you mean by this... Which variables are you referring to,
and where are they being declared?