Still save-dialog prompt after displayalerts set to false

[stef.ong]

This is some code I used in a macro :

Application.DisplayAlerts = False
ActiveWindow.Close SaveChanges:=False
Application.DisplayAlerts = True

On some PC it is closing the file without prompting a dialog to save or not save the file. However on another PC it still prompt for this info ???
Same Excel 2000 SP3 is used on all 4 PC

Does anyone has a clue ??

 

[erik.van.geit]

Hi,

not sure at all if I can help, but let's give it a try

if you have
WB.Close SaveChanges:=False
I don't think Application.DisplayAlerts = False is even necessary
it's a great mistery when everything is identic ...
I suspect it only looks like identic and there is something different.

some tests
put before the close-line

WB.Saved = True

put this in the workbookmodule of the problem-workbook

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "sheet " & Sh.Name & " is calculated"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "sheet " & Sh.Name & " is changed"
End Sub

and in your code perhaps add a msgbox

WB.Saved = True
MsgBox "from here no calculation would be normal: not any popup should come up"
WB.Close False

kind regards,
Erik

 

[stef.ong]

Erik,

Indeed I tried it without the displayalerts and it was also not showing an alert on my PC. I added the workbook.saved=true trick and this is also OK on my PC but on the other ... still the same problem.

In the mean time we found out that on the other PC whenever there is a file opened which contains a macro it asks to save the file or not even if nothing was done in that file. Doing the same but opening a file without a macro nothing is asked.

To me it seams an excel option/setting problem. Any idea ?

Stef.

 

[BJungheim]

Try adding this line right before the closed line:

wbQoute.Saved = True

This should convince Excel that it doesn't need to pop up with a dialog since it thinks the file was already saved.

 

[erik.van.geit]

Stef,

WB.Saved = True not working is really strange
BJungheim, we already mentioned at least two times this line

some new ideas:
1. is there code in the personal.xls on that particular computer ?

2. not sure at all if this would provide you relevant errors in the code:
do you have Option Explicit on top of your modules ?

I called some guys, hoping they would know & respond.

best regards,
Erik

 

[tusharm]

Why would you think it is an XL option if you've localized the problem to a particular file being open?

Does that file contain a _BeforeClose or a _BeforeSave macro? Particularly at the application level?

Also, your code operates on Active{xxxx} entities. As someone has already suggested, you should be more specific and operate on the object you really want.

Combine the above and the problem is that the code in the other workbook is responding to activities related to this the workbook of interest and generating the alert(s).

One way out -- and this is but a band-aid -- is to use the untested

on error resume next
application.enableevents=false
{your save/close code}
application.enableevents=true

There are many reasons why adding code to workbooks is not a good idea. Lacking a "global" plan -- and not anticipating that other code will execute while your code also is -- can cause all kinds of problems.

See
Monitoring events
http://www.tushar-mehta.com/excel/vba/vba-XL events.htm

Erik,

Indeed I tried it without the displayalerts and it was also not showing an alert on my PC. I added the workbook.saved=true trick and this is also OK on my PC but on the other ... still the same problem.

In the mean time we found out that on the other PC whenever there is a file opened which contains a macro it asks to save the file or not even if nothing was done in that file. Doing the same but opening a file without a macro nothing is asked.

To me it seams an excel option/setting problem. Any idea ?

Stef.

 

[stef.ong]

Erik, Thusharm,

The problem is found and solved !!!

The add-in for 'analysis toolpack - VBA' was checked. This caused the problem.
I first added the lines ' Application.EnableEvents' in the code and then no dialog was opened anymore ( Thanks Thusharm). I looked then in the visual basic editor to find out that more projects were opened : 'funcres' and 'atpvbaen'. After some googling I could find that this was related to the analysis add-in.
After uncheck the add-in it is also working without the EnableEvents.

Thanks a lot for your help!!!

Regards,
Stef.

 

[stef.ong]

Erik, Thusharm,

The enableevents=false has the result that the dialog to save is not shown anymore, this is great but ...

The way I'm using my vba-script is as follows.

I open a file with the script depending on the name I do change some visible settings to show some command buttons, this is done in the 'workbook_open' part. After this a copysave of this file is done and in that same 'workbook_open' part those command buttons are set to unvisible and another is set visible.
When de original file is then closed by the script I can continue as I it should be.
The problem now is when I close this file and reopen the original file it will not execute the 'workbook_open' part since the enableevents is on false for the application.

Is there a way to overrule this ?

Stef.

 

[erik.van.geit]

Hi, Stef,

is it too obvious to ask "why didn't you set enableevents to TRUE" within the code ?

I assume the following is done manually ?
or is it by code ?

when I close this file and reopen the original file it will not execute the 'workbook_open' part since the enableevents is on false for the application.

best regards,
Erik

 

[stef.ong]

Eric,

All the closing of the files is done within the code. Because I wanted to get rid of the dialog box I set the EnableEvents on false before the file is closed.
In code I forsee a line to set this enableevents flag on true again but, since the file is closed, this line will not be executed. At the end I have the Excel application open without having a workbook open and the enableevent on false.
At that point, if I open a workbook, it won't execute the code written in the 'workbook_open' part.

I should be able to force the enableevent to true after a file was closed or if that is absolutly impossible, make sure the code in 'workbook.xls' is always executed.

Stef.

 

[erik.van.geit]

Hello, stef,

this is a matter of structure

you should find a way to set the events to TRUE again before the code ends
I think, I can provide a workaround, calling another workbookprocedure with OnTime, but it would be ugly-code. No, you need to structure the code. The workbook housing the code must stay open till the events=true-line has run. Or it could be time to check out thusharm's site !

kind regards,
Erik