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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,435
Messages
6,136,607
Members
450,021
Latest member
Jlopez0320

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
Back
Top