Enablings Macros on Protected Sheets

[gsq797]

I basically want to enable a button that refreshes a pivot at the same time protect all the cells and formulas contained in that same worksheet.

Any code suggestions?? Quite lost.

 

[brooksres]

you have add a line to your code for the macro:

ActiveSheet.Unprotect Password:="your password"

then at the end, just before End Sub Add

ActiveSheet.Protect Password:="your password"


This will allow your macro to disable your password protection, excute the macro and reprotect itselt all with one click.

 

[Bartek]

Hi,

Worksheet can by protected with option AllowUsingPivotTables set to True, but I am not sure whether this allows refreshing Pivots. Additionally, this code may not work with older version of Excel (I am not sure, but some options like AllowFormattingCells are only available since Excel XP).

You could unprotect the worksheet, refresh pivots and reprotect, as suggested above. Such method has only one drawback - if execution in interrupted by user by pressing ESC or Ctrl+Break the worksheet will be left unprotected. You may protect against this with:

Application.EnableCancelKey = xlDisabled
(your code...)
Application.EnableCancelKey = xlInterrupt

Of course, always be very careful when disabling cancel key.

 

[gsq797]

Brooksres!

You're awesome =)