CODEWITHSUNDEEP
excelvba
Rodger
Rodger

Reputation: 29

Can't make Application.DisplayAlerts = False in Excel 2016 VBA

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.

Upvotes: 2

Views: 6772

Answers (1)

pstraton
pstraton

Reputation: 1120

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.

Upvotes: 3

Related Questions