Closed Thread
Results 1 to 11 of 11

Application.DisplayAlerts = False

  1. #1
    Jim May
    Guest

    Application.DisplayAlerts = False


    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


  2. #2
    Norman Jones
    Guest

    Re: Application.DisplayAlerts = False

    Hi Jim,

    Try:

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


    ---
    Regards,
    Norman



    "Jim May" <[email protected]> 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
    >




  3. #3
    Jim May
    Guest

    Re: Application.DisplayAlerts = False

    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" <[email protected]> wrote in message
    news:#[email protected]:

    > Hi Jim,
    >
    > Try:
    >
    > Tools | Options | Edit | Uncheck "Ask to update automatic links"
    >
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "Jim May" <[email protected]> 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
    > >



  4. #4
    Norman Jones
    Guest

    Re: Application.DisplayAlerts = False

    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



  5. #5
    Don Wiss
    Guest

    Re: Application.DisplayAlerts = False

    On Sun, 13 Aug 2006 02:37:39 +0000, "Jim May" <[email protected]> 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).

  6. #6
    Norman Jones
    Guest

    Re: Application.DisplayAlerts = False

    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



  7. #7
    Tom Ogilvy
    Guest

    Re: Application.DisplayAlerts = False

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  8. #8
    Norman Jones
    Guest

    Re: Application.DisplayAlerts = False

    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



  9. #9
    Jim May
    Guest

    Re: Application.DisplayAlerts = False

    Much Appreciated, Guys
    Jim

    "Norman Jones" <[email protected]> wrote in message
    news:#[email protected]:

    > 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



  10. #10
    Registered User
    Join Date
    08-18-2006
    Posts
    1

    Another problem

    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

  11. #11
    NickHK
    Guest

    Re: Application.DisplayAlerts = False

    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" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
    >
    > 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
    >




Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1