Another Native Database Query warning popup question

Delli gatti, Joseph 1 Reputation point
2021-06-25T21:23:09.423+00:00

I use PowerQuery in Excel to provide an easy-to-refresh reporting experience for analysts and manager-level people.

Using M-Query, SQL, and a couple of simple VBA macros makes these reports awesome... except for all of the dialog boxes and warnings that freak them out the first time they use them or download a fresh copy of the document from SharePoint.

Someone mentioned in a related discussion that database permissions should ensure that the end-report user doesn't have power to make changes or violate their access privileges through the report. That would seem reasonable to me if some people running the reports didn't have dbo-type permissions. Conceivably, someone could make an erroneous SQL code fix in an Excel report, and then the next person who opened the Excel document and ran the report would potentially end up stuck with the consequences and maybe the blame for those changes.

My thinking is that if you could password protect worksheets from being edited, why not also allow VBA code and queries to be password protectable? Then you could ditch the unprofessional-looking warnings and dialogs that popup when someone tries to run an enhanced report.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,962 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,640 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viki Ji_MSFT 4,411 Reputation points
    2021-06-28T07:09:45.947+00:00

    @Delli gatti, Joseph ,

    Welcome to Q&A forum!

    According to your description, you could try to disable Alert/Warning Messages by using some code in Excel. I fould an official article Application.DisplayAlerts property (Excel), you may have a look.
    Besides, due to we are more focused on general issues about Excel client, if you need more help about VBA code, please refer to Office VBA support and feedback.

    Per my research, you may try to protect your VBA with password by the following steps.

    • Open the Excel file, Press Alt+F11 to open VBA edior.
    • Click Tools>VBA Project properties>Protection>check Lock project for viewing>enter and confirm a password>OK in turn.

    109742-image.png

    Regarding to protect the queries, please go to Review>Protect Workboot>check Structure>enter password>OK to check whether it works.

    109727-image.png

    Any updates or misunderstandings, please let me know.

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.