How to find what causes the privacy warning in Excel?

Question

There is this Excel file I made, it has no macros or any such things, it's just all formulas, and I copied some data into it from various sources (ERP Excel export, copy-pasting from web applications...), but now I get the well-known privacy warning every time I save the file - or when it auto-saves. Which is very annoying.

I know it is possible to disable the warning on my computer (How can I prevent the privacy warning in Excel?) but it does not explain what caused it to appear (other files I made are fine), and I cannot reasonably tell all the people I have to send this file to in a professional environment to go and tweak their settings just for my file not to annoy them...

Is there any way I can find what causes this warning to appear? I have no macro, no XML, web, or any other external data sources...

Answer 1

Once the privacy warning has triggered on a spreadsheet there isn't a way to get rid of it. You may have briefly had something copied that caused it to trigger. This is unfortunately a problem for troubleshooting what exactly caused the problem as you can't fix it on the workbook to remove the warning.

The only fix that exists currently that I'm aware of is to create a new sheet and copy your formula/data into there.

Answer 2

That is actually not the case.

Every time I've got a privacy warning, I have been able to remove it, by adding one line of code.

It happens when you create an object in your code, and you don't "erase" it afterwards.

example: I want to add a button in my sheet by using a macro.

btnTemp = ActiveSheet.Buttons.Add(6, 6, 100, 60).Select

This code actually creates an object floating somewhere inside the file, and this is what triggers the warning.

If I add the following code, and run it again, the warning is gone.

Set btnTemp = Nothing

So look for any object that have not been erased/reset. That will probably fix it!