General situation:
we have our work data in a database which I load into a data model where I set up my requirements via power query and the results then get loaded into a pivot table. On top of that I created a mask to enter a date and a selec string gets created which I successfully inject into the data model so the actually wanted data gets loaded into the data model. This works via a macro that simply injects a cell - containing the formula - into the data model and simply executes a ActiveWorkbook.RefreshAll(). This works flawlessly for me - who made the file - and I get data loaded into the model only from the given date and the pivot also automatically updates and shows the relevant entries of the yesterday.
Problem:
When my teammates hit the update macro Excel opens a prompt asking about security. They then have to check the box saying to ignore the security and save. It updates for them then. However, they have to do this every time they reopen the file unless they save the file itself - which they shouldn't do so they can't accidently bust up formulars - and even then, only the one who last saved it doesn't get prompted again.
Example: I - the creator - can always update without an extra prompt, no matter what.
Member A updates, checks the "ignore", saves the prompt and then saves the
whole file. He can now always update without the extra prompt.
Member B updates and gets the prompt. If he checksand saves "ignore" and
then saves the whole file, he can now always update without the
extra prompt, but now Member A lost that privilege and as soon as he
updates he gets the extra prompt again.
I don't understand what exactly is happening here. Especially because the older files simply work with a table - db data gets imported into an actual Excel table instead of a data model - and that works just fine without any extra prompts.
Is there a way to generally disable this security prompt?
I will attach a screenshot of the prompt, in german, though.
Thanks for your help in advance.
with best regards, ciradan.
