At my job we have monthly reports (in Excel) that are sent to many people. Last year someone changed our data and then presented it as evidence that our report was WRONG. Luckily, we had the originals that were sent out and could prove this person wrong. This year my boss wants the spreadsheets to be locked down. We have just gotten everything converted for the new year (and a new organization structure) with all the additional elements that the higher ups want. I locked everything down, and then I found a loophole. One of our spreadsheets has macros that will filter for certain areas on all five tabs at the same time. To make this work I placed a password on a hidden tab and had the macro reference that and then unprotect, filter, and re-protect the worksheets. The loophole that I found is that while the password isnt in the code, protecting the workbook doesnt prevent someone from going to the Developer tab, clicking on Macro and running the Unprotect All macro. I could remove all the macros, have a filter for the sections and just manually protect and unprotect each tab. Thats a lot of work. Short of that, is there an alternative? I tried the Lock project for viewing option on the Protection tab, but that did nothing for my situation.
TIA
-Monica