Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    If you look up DisplayAlerts in the Excel VBA help, you'll see that Excel resets it to True at the end of a procedure.

    If you look up DisplayAlerts in the Word VBA help, you'll see that Word does *not* reset it to True at the end of a procedure.

    So the difference in behavior is 'by design'.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    Sorry, forgot about PowerPoint.

    The PowerPoint 2002 VBA help claims the same behavior for DisplayAlerts as Word, but it acts the same as Excel: it is reset at the end of a procedure.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Application.DisplayAlerts = False (Excel/PPT)

    Hi Hans
    I was debating whether to send off a message commenting on your speed of response. I cannot let it go without comment now that you have also demonstrated your thoroughness.
    Regards
    Don

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    NIMBY! (Excel 2000 VBA help)
    [i]"DisplayAlerts Property: True if Microsoft Excel displays certain alerts and messages while a macro is running. Read/write Boolean.
    Remarks: The default value is True. Set this property to False if you don

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    "If you set this property to False, Micorosoft Excel sets this property to True when the code is finished, unless you are running cross process code."

    I don't see this anywhere at all in my Excel VBA help files. Might you please post the version(s) In which you see this?
    I'm going to a friend's wedding now. Not sure whether I'd rather be there or here (grin!). I'll be back when we've eaten out the buffet at The Mandarin.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    I'm using the Dutch language version of Office XP (2002), so there's not much use quoting back the help text to you <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    But see the reply by Don Wells.

    (In Excel and PowePoint, the test code returns True every time, as reported by you)

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Application.DisplayAlerts = False (Excel/PPT)

    <P ID="edit" class=small>(Edited by wdwells on 14-Oct-06 13:32. Updated with info on Excel 97)</P>Hi Chris
    I'm running Excel 2003 SP2.
    FWIW Excel 97 specifically states that it does NOT set it back to true. I don't have a machine at home with 2000 any more but will check on my 2K machine at work on Monday and advise.
    Regards
    Don

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    I have managed to get into a situation with Excel where displayalerts persisted to false, making me loose my work because I didn't get the save changes message when I closed the workbook.

    Cannot reporduce the situation though, but it has made me set it back to true everytime I have set it to false.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    I too always set DisplayAlerts to True at the end of a procedure explicitly if I have set it to False earlier on. Even though it may not strictly be necessary, it makes the code look more consistent, and you never know...

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    > " ... Your macro should always set the property back to True when it stops running ..."

    Might be appropriate to include an Application.DisplayAlerts = True in one of those error-handlers you "abhor" <post:=605,010>post 605,010</post:> <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Alan

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Application.DisplayAlerts = False (Excel/PPT)

    The code below does what I expect in Word97/2000/20003, but not in any of Excel97/2000/2003, and not in PPT2003.
    "Application.DisplayAlerts" is unavailable in PPT97/2000.
    In particular, in Excel, the macro TESTboolHideAlerts always returns a message box with TRUE, whereas in Word it flips to False after the first call, as I expect it should.

    <pre>Public Function blnShowAlerts() As Boolean
    ' Procedure : blnShowAlerts
    ' Description: Set the environment.
    ' Copyright: Christopher Greaves
    ' Inputs: None.
    ' Returns: Previous seting of alerts.
    ' Assumes: None.
    ' Side Effects: None.
    ' Tested: By a call from the user.
    blnShowAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = True
    End Function
    Sub TESTboolShowAlerts()
    MsgBox blnShowAlerts
    End Sub

    Public Function blnHideAlerts() As Boolean
    ' Procedure : blnHideAlerts
    ' Description: Set the environment.
    ' Copyright: Christopher Greaves
    ' Inputs: None.
    ' Returns: Previous seting of alerts.
    ' Assumes: None.
    ' Side Effects: None.
    ' Tested: By a call from the user.
    blnHideAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    End Function
    Sub TESTboolHideAlerts()
    MsgBox blnHideAlerts
    End Sub</pre>


  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    Hans, thanks for the reply.
    > there's not much use quoting back the help text to you
    I dunno. Might make just as much sense as (what appears to be) my non-standard English-language version of help.

    >In Excel and PowePoint, the test code returns True every time, as reported by you
    Right.
    <pre> blnHideAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False</pre>

    In the snippet above from "blnHideAlerts", I single-step through the code in Excel2000 and note that the (mouse-hover) value of .DisplayAlerts remains set at "True" after the statement has been executed.

    Regardless of the crud in the Help files (at my end), the assignment of "Application.DisplayAlerts = False" seems not to be taking effect.

    When I single-step in Word2000, the value switches from -1 to 0 after execution.

    Same code in both cases, carefully pasted from my original post. Nothing up my sleeve, ....

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    > it has made me set it back to true everytime I have set it to false.
    Agreed. I'm all in favour of good, clean programming, nested in both coding at execution.
    Now, if only I could get it to work (as per my reply to Hans)

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    > FWIW Excel 97 specifically states that it does NOT set it back to tru
    It's worth a lot to me. First I can't get the code to work, then I begin to have doubts about the quality of my Help files.

    The text below in italics is pasted from my Excel97/VBA help.

    "doesn't automatically set it back to True "
    This is what I like to see, and it is how I'd like it to be.
    I note too that it says "macro", which is, after all, an end-user device. After a macro stops running, it makes sense to reset some (or all) of the run-time states to safe default values.
    Of course, some programmers (not me!) use subroutines without arguments as part of their code, so if VBA is being picky, it might be resetting (or not) to default values "after execution of a subroutine without arguments", which can be a different event from "after completing all execution associated with the end-user running a subroutine without arguments". Both are macros, only one represents an end-user completion of task and return of control to the end user.

    "different from previous versions of Microsoft Excel"
    No guarantees here, but I'd like to think that this represented a permanent change in design, that is, the statement should hold true for 97 and all futture versions.

    "DisplayAlerts property was automatically reset to True "
    As noted in my response to Hans, it now seems to me that in Excel2000 the value is not being set at all, let alone preserved or reset by default.

    If you set this property to False, Microsoft Excel doesn't automatically set it back to True when your macro stops running. Your macro should always set the property back to True when it stops running. Note This behavior is different from previous versions of Microsoft Excel. In earlier versions, the DisplayAlerts property was automatically reset to True when the macro stopped running. If you have old code that relies on this behavior, you should change your code to explicitly set the property back to True at the end of the macro.

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.DisplayAlerts = False (Excel/PPT)

    > Might be appropriate
    Right. I'll consider that (if ever I find a good reason to use an On Error (grin!))

Page 1 of 2 12 LastLast

Posting Permissions

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