Application DisplayAlerts = False not working

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
158
I have searched the board. No one seems to have an answer for this. Any new updates? Sometimes it works, sometimes it doesn't. Is there something in a file that makes it not work?
Thanks for any insight!
Rasberry
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Can you post the code where it does not do what you want it to?
What kind of Alert is getting through?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

assuming you are talking about the popup when closing a file
you could put this in your workbookmodule
if you want to save use first option, else next one
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'to save
ThisWorkbook.Close True
'or not to save: that's the question !
ThisWorkbook.Close False
'alternatively
'ThisWorkbook.Saved = True
End Sub
kind regards,
Erik
 

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
158

ADVERTISEMENT

Well, before I do that, I still want to make sure that I understand what is going on. I have 39 files that it opens, copies some info and pastes it into another worksheet (if the info is there). Then it closes the .xls file and goes onto the next. On the first 25 of the files the DisplayAlerts = false works. On the remaining 14 it does not. However, even if I switch the order, it still doesn't work on the remaining 14. I can't find a common denominator, yet in those 14 files that is not in the other 25.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
"volatile" functions are updated when opening a workbook

example
create workbook
type =NOW() in any cell
save and close
open again and close: you will get a popup

so I suspect those 14 contain volatile functions
in your case you can use something like

Code:
Open ....
Set WB = ActiveWorkbook
'code to copy
WB.Close False

for more info see
http://support.microsoft.com/default.aspx?scid=kb;en-us;q274500

best regards,
Erik
 

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
158

ADVERTISEMENT

Well, I can't find any volatile code, but there are charts in the workbooks. However there are also charts in the workbooks that skip the popup message. Here is my code. Anyway around the message asking me to save it?

Columns("A:A").Select
Selection.Find(What:="end", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
NumberOfFiles = ActiveCell.Row
Range("A4").Select
For I = 1 To NumberOfFiles - 1
DataGathering:
ActiveCell.Offset(1, 0).Select
Filename = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
WorksheetName = ActiveCell.Value
ActiveCell.Offset(0, -1).Select
Workbooks.Open Filename:="G:\analysis\MASTER\A\" & Filename
Sheets(WorksheetName).Activate
' Error handling if it can't find value
On Error GoTo err_chk

' Find value and copy cell under it
Range("A1").Select
Cells.Find(What:="ROIC", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Offset(1, 0).Copy

' Go to new location and paste
Windows("Credit_Statistics_Report.xls").Activate
Sheets("StatSheet").Activate
ActiveCell.Select
ActiveCell.Offset(0, 8).Select
ActiveSheet.Paste
ActiveCell.Offset(0, -8).Select
Windows(Filename).Activate
Application.CutCopyMode = False
Application.DisplayAlerts = False
Workbooks(Filename).Close
Application.DisplayAlerts = True
Next I

' Error handling
err_chk:
Windows("Credit_Statistics_Report.xls").Activate
Sheets("StatSheet").Activate
ActiveCell.Select
ActiveCell.Offset(0, 8).Select
ActiveCell.FormulaR1C1 = "N/A"
ActiveCell.Offset(0, -8).Select
Windows(Filename).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Windows("Credit_Statistics_Report.xls").Activate
Resume ZeroErrors
ZeroErrors:
On Error GoTo 0
GoTo DataGathering
End Sub
 

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
158
Thanks for the heads up on updating cells. I fixed all but one spreadsheet. No on to the next task. Rasberry
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Thanks for the heads up on updating cells. I fixed all but one spreadsheet. No on to the next task. Rasberry
this looks like "solved" to me
or just that "one spreadsheet" ... ??
 

Watch MrExcel Video

Forum statistics

Threads
1,114,060
Messages
5,545,761
Members
410,704
Latest member
Cobber2008
Top