2

I have been chasing my tail for weeks, with more hours "Googling around than I want to admit.

I have a large, complex analytical app within an Excel 2016 spreadsheet that captures SQL table data, queries email data and does a lot of stuff that I think is pretty cool. Users see sales force automation performance metrics and charts. All is well except for one thing. I cannot stop the "save data?" dialog box from appearing no matter what I do.

As a workaround I've put the spreadsheet on on a network ride and given users a shortcut that runs a VBscript to copy this spreadsheet to a hidden drive on the local PC and runs it. So if they save it, there's no worries as they aren't working with the original data. But as one would easily imagine, the load time is necessarily longer than it need be and users are confused by a message when I am telling them they can't save the data.

Net of a lot of different experiments, it seems like I've uncovered a bug in Excel (yeah, I know, this sounds lame even to me) as I cannot make the Application.DisplayAlerts = False. It just will not take.

See image here: enter image description here

The image above (or at the above link as I haven't submitted enough question yet to embed images) is obviously taken from the Immediate Window when I was running the app. I entered the steps in the exact order shown. Note that I set Application.DisplayAlerts = False and then checked the value immediately afterwords and it was True.

Very weird. Is this a bug?

One last aside that is probably irrelevant; I was using .XLSB format because of the smaller footprint, much shorter load time and to get around PC setup issues with macros. But I've switched back to .XLSX to simplify the experiment.

Improve this question
9
  • You can not set DisplayAlerts to false outside of a sub... this way the immediate window will never show the true value... but you can create a sub with Stop and test it again while the sub is at halt... it should now show false... but as soon as the sub ends, it will be true again... Commented Aug 1, 2016 at 21:38
  • MSDN: If you set this property to False, Microsoft Excel sets this property to True when the code is finished, unless you are running cross-process code. msdn.microsoft.com/en-us/library/office/ff839782.aspx Commented Aug 1, 2016 at 21:39
  • Also it should be wrapped inside a EnableEvents = False... then nothing should pop up as long as any sub is running... Commented Aug 1, 2016 at 21:42
  • I am setting to false within a sub and just used the imnmeidate window to display for ease of explanation. I've read read about "cross process" but don't know exact how this is defined. I set the value to false after displaying a message in Workbook_Open() Commented Aug 1, 2016 at 21:44
  • In short: as soon as no sub is running anymore (no vba code is in active processing) the value will be set to true again... Commented Aug 1, 2016 at 21:50

1 Answer 1

Reset to default
3

To clarify: the code temporarily halts after each single-step in the debugger, resulting in Excel setting it back to True while idle. Here’s a way to verify this behavior:

  1. In the VBE code-development window, open the Immediate window (Ctrl-G).

  2. Temporarily insert the following code somewhere in your routine:

    Debug.Print "..."

    Application.DisplayAlerts = False

    Debug.Print "Application.DisplayAlerts: " & Application.DisplayAlerts

    Stop

  3. Using the “Set Next Statement” tool (or Ctrl-F9), set the Debug.Print "..." statement to be the next one to execute.

  4. Run the code form there by pressing F5 (Continue).

  5. You should see this displayed on the Immediate window:

    ...

    Application.DisplayAlerts: False

  6. Now, with the code stopped at the Stop statement, type “?Application.DisplayAlerts” in the Immediate window, and press the [Enter] key. You will get:

    ?Application.DisplayAlerts

    True

    because Excel has reset it to True while the code is suspended.

  7. Remove the experimental code.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.