I have an excel spreadsheet that I want to distribute to everyone in my department. The file is linked to a list on SharePoint with several thousand names. Right now, I have an advanced filter set up (but hidden) so that anyone (regardless of excel ability) can type a name the search box and click the (macro enabled) search button to find a name in the large list. The macro that I recorded refreshes the data from SharePoint and reapplies the advanced filter every time the search button is clicked.

I want the sheet to be protected so that people cannot alter or delete anything in the file. Unfortunately, when I protect the sheet (with a password), the macro won't run. I tried to work the unprotecting and re-protecting of the worksheet into the macro, but the password dialog box pops up while the macro is running.

I have searched online for a solution, and it sounds like there is, but it involves VBA, which I know nothing about. My macro ability is limited to recording macros.

Any help would be appreciated.

Thanks!