Enablings Macros on Protected Sheets

gsq797

New Member
Joined
Sep 19, 2006
Messages
49
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

brooksres

Board Regular
Joined
Aug 4, 2006
Messages
74
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

Board Regular
Joined
Jul 29, 2006
Messages
54
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:

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

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

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top