Running a macro on a protected worksheet - runtime error 1004

Hi all,

I was wondering if someone might be able to help please?

Short version:
I'm getting a runtime error 1004 on a doc when trying to run a macro on a protected sheet.

Long version:
I'm using Excel 2013 (my company hasn't updated me to 2016/365 yet!). I have a series of pivot-tables reading from a master sheet and feeding data into graphs/tables/maps in a dashboard. This dashboard is to go on our website and is for use by the general public.

In the dashboard I want to be able to flip the values in a series of text boxes on a map between "headcount" and "full-time equivalents (FTE)" (a calculation that factors in part-timers and full timers and extrapolates to a workforce as a whole - in case you're not familiar with this term).

Because the data from the master datasheet must be pivoted in several different ways and then combined with loads of calculations in order to get the FTE calculation, I need to then feed the original pivoted data into a new table that does all the calcs and then that new summary table is pivoted from. This latter pivot-table controls how the data will appear in the text boxes on the map.

So the order goes like this:

1. Master datasheet ->
2. Pivots (connected to a series of slicers in the doc) ->
3. Summary table bringing data from multiple pivots in (2) and then performing calcs ->
4. Another pivot ->
5. Map values

Every time a slicer connected to the first set of pivot-tables (2 in the list above) is clicked, it updates those pivots, but not the pivot connected to the new summary table (3 in the list above). The solution to that was to build a button, write REFRESH on it, and assign a macro to it that refreshes the new pivot-table (4 in the list above) each time that refresh button is pressed. It's a bit Rube Goldberg, but it works.

However, seeing as this is a dashboard for the general public I want to password-protect it. But if I protect the sheet I get a runtime Error 1004 every time the refresh button is clicked, saying that I can't run macros on a protected sheet.

I hope that's clear enough?! Please can someone advise how to get around this?

BTW - I'm fine in most aspects of Excel, but I'm not a coder so I might ask stupid questions about any code/etc you suggest!

Thanks for your help.

C