Application DisplayAlerts = False not working

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
195
Office Version
  1. 365
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Can you post the code where it does not do what you want it to?
What kind of Alert is getting through?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
"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
 
Upvote 0
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
 
Upvote 0
Thanks for the heads up on updating cells. I fixed all but one spreadsheet. No on to the next task. Rasberry
 
Upvote 0
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" ... ??
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top