# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Application.DisplayAlerts = False

## Jim May

In a Macro I have:
......
Application.ScreenUpdating = False
Application.DisplayAlerts = False
....
Set wb = Workbooks.Open(sPath & sName)
Do While sName <> ""

XXXXXXXXXXXXXXXXXX  << Problem here

Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True


As the Second Workbook is Opened I get
The Standard Message Warning/Alert
Dealing with Updating Links;
Update; Don't Update;

I thought my Original DisplayAlerts = False
Took care of this?

Can someone assist?

Jim

----------


## Norman Jones

Hi Jim,

Try:

Tools | Options | Edit | Uncheck "Ask to update automatic links"


---
Regards,
Norman



"Jim May" <jmay@cox.net> wrote in message
news:TfwDg.239$xk3.35@dukeread07...
>
> In a Macro I have:
> .....
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> ...
> Set wb = Workbooks.Open(sPath & sName)
> Do While sName <> ""
>
> XXXXXXXXXXXXXXXXXX  << Problem here
>
> Loop
> Application.ScreenUpdating = True
> Application.DisplayAlerts = True
>
>
> As the Second Workbook is Opened I get
> The Standard Message Warning/Alert
> Dealing with Updating Links;
> Update; Don't Update;
>
> I thought my Original DisplayAlerts = False
> Took care of this?
>
> Can someone assist?
>
> Jim
>

----------


## Jim May

Thanks Norman;
Is this one of those situations where this affects All Workbooks
Until you return to Tools Options, Edit and Check it back on?  Also
Unchecking this does it mean that all links are updated?
Tks,
Jim

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:#nyGJdovGHA.560@TK2MSFTNGP05.phx.gbl:

> Hi Jim,
>
> Try:
>
> Tools | Options | Edit | Uncheck "Ask to update automatic links"
>
>
> ---
> Regards,
> Norman
>
>
>
> "Jim May" <jmay@cox.net> wrote in message
> news:TfwDg.239$xk3.35@dukeread07...
>
> >
> > In a Macro I have:
> > .....
> > Application.ScreenUpdating = False
> > Application.DisplayAlerts = False
> > ...
> > Set wb = Workbooks.Open(sPath & sName)
> > Do While sName <> ""
> >
> > XXXXXXXXXXXXXXXXXX  << Problem here
> >
> > Loop
> > Application.ScreenUpdating = True
> > Application.DisplayAlerts = True
> >
> >
> > As the Second Workbook is Opened I get
> > The Standard Message Warning/Alert
> > Dealing with Updating Links;
> > Update; Don't Update;
> >
> > I thought my Original DisplayAlerts = False
> > Took care of this?
> >
> > Can someone assist?
> >
> > Jim
> >

----------


## Norman Jones

Hi Jim,

> Is this one of those situations where this affects All Workbooks
> Until you return to Tools Options, Edit and Check it back on?

As you correctly surmise, this is an application setting; if you are
distributing the workbook, note that your users would similarly need to
adjust the setting.

> Unchecking this does it mean that all links are updated?

Yes.

---
Regards,
Norman

----------


## Don Wiss

On Sun, 13 Aug 2006 02:37:39 +0000, "Jim May" <jmay@cox.net> wrote:

>As the Second Workbook is Opened I get
>The Standard Message Warning/Alert
>Dealing with Updating Links;
>Update; Don't Update;
>
>I thought my Original DisplayAlerts = False
>Took care of this?

Try Application.AskToUpdateLinks = False

Don <www.donwiss.com> (e-mail link at home page bottom).

----------


## Norman Jones

Hi Jim,

As Don indicates in an adjacent post, you can change the application setting
programmatically.

If you wish ajust the setting for cetain files only, you can use the
UpdateLinks argument to the Worbook's open method. This argument takes four
values:

0     Doesn't update any references
1     Updates external references but not remote references
2     Updates remote references but not external references
3     Updates both remote and external references

See VBA help on the Open method for more information.


---
Regards,
Norman

----------


## Tom Ogilvy

Just a thought:
> If you wish ajust the setting for cetain files only, you can use the

The end result will be the same, but to me it would be better to say not
that it is changing the setting for that workbook but  it is specifing the
action to take for that single opening of the workbook.

Set wb = Workbooks.Open(sPath & sName, Updatelinks:=0)

--
Regards,
Tom Ogilvy


"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:OqQEQ6ovGHA.4160@TK2MSFTNGP06.phx.gbl...
> Hi Jim,
>
> As Don indicates in an adjacent post, you can change the application
> setting programmatically.
>
> If you wish ajust the setting for cetain files only, you can use the
> UpdateLinks argument to the Worbook's open method. This argument takes
> four values:
>
> 0     Doesn't update any references
> 1     Updates external references but not remote references
> 2     Updates remote references but not external references
> 3     Updates both remote and external references
>
> See VBA help on the Open method for more information.
>
>
> ---
> Regards,
> Norman
>

----------


## Norman Jones

Hi Tom,

>> If you wish ajust the setting for cetain files only, you can use the

> The end result will be the same, but to me it would be better to say not
> that it is changing the setting for that workbook but  it is specifing the
> action to take for that single opening of the workbook.

I agree; there is an unfortunate, and unintended ambiguity, in my phrase!
Certainly, my intention was to indicate a meaning synonymous with your
articulation.

Thank you for highlighting a possible source of misunderstanding.

--
---
Regards,
Norman

----------


## Jim May

Much Appreciated, Guys
Jim

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:#VJoj9rvGHA.2436@TK2MSFTNGP06.phx.gbl:

> Hi Tom,
>
>
> >> If you wish ajust the setting for cetain files only, you can use the
>
>
> > The end result will be the same, but to me it would be better to say not
> > that it is changing the setting for that workbook but  it is specifing the
> > action to take for that single opening of the workbook.
>
>
> I agree; there is an unfortunate, and unintended ambiguity, in my phrase!
> Certainly, my intention was to indicate a meaning synonymous with your
> articulation.
>
> Thank you for highlighting a possible source of misunderstanding.
>
> --
> ---
> Regards,
> Norman

----------


## kotelok

I have several report templates (spreadsheets) in one workbook. A user can select one (or more) reports in vb screen (application is written in vb 6), data from a database are processed and send to the Excel template - then unnecessary worksheets are deleted. It works perfectly at the first run. If I repeat the procedure - Application.DisplayAlerts=False does not work, in other words excessive worksheets are not being deleted. Magic!
This is the procedure:
Public Sub RemoveExtraSheets(ByRef s As String)

Dim eItem As Excel.Worksheet

For Each eItem In xlAppl.ActiveWorkbook.Worksheets

 Application.DisplayAlerts = False

        If InStr(eItem.Name, s) = 0 Then   
        eItem.Delete
         End If
Next
 Application.DisplayAlerts = True

End Sub

----------


## NickHK

I would imagine the
InStr(eItem.Name, s)=0
is evaluating to False

If you run it on the same workbook, the first time all the WS are deleted,
so there are no more that qualify the second time.

NickHK

"kotelok" <kotelok.2cquff_1155918007.0754@excelforum-nospam.com> ¼¶¼g©ó¶l¥ó·s»D:kotelok.2cquff_1155918007.0754@excelforum-nospam.com...
>
> I have several report templates (spreadsheets) in one workbook. A user
> can select one (or more) reports in vb screen (application is written
> in vb 6), data from a database are processed and send to the Excel
> template - then unnecessary worksheets are deleted. It works perfectly
> at the first run. If I repeat the procedure -
> Application.DisplayAlerts=False does not work, in other words excessive
> worksheets are not being deleted. Magic!
> This is the procedure:
> Public Sub RemoveExtraSheets(ByRef s As String)
>
> Dim eItem As Excel.Worksheet
>
> For Each eItem In xlAppl.ActiveWorkbook.Worksheets
>
> Application.DisplayAlerts = False
>
> If InStr(eItem.Name, s) = 0 Then
> eItem.Delete
> End If
> Next
> Application.DisplayAlerts = True
>
> End Sub
>
>
> --
> kotelok
> ------------------------------------------------------------------------
> kotelok's Profile:
> http://www.excelforum.com/member.php...o&userid=37725
> View this thread: http://www.excelforum.com/showthread...hreadid=571110
>

----------

