Macroing a protected sheet

snipescc

Board Regular
Joined
Aug 29, 2010
Messages
136
Office Version
  1. 365
Platform
  1. Windows
Hi fellow Excel geeks. I recently switched to Excel 2016, so I'm not sure how to handle a VBA issue. I'm making a report for our low-level managers, many of whom don't have great computer skills, so I want to make it as easy as possible. They copy data exported from our database into a tab. The important stuff is then pulled into another tab that is protected, and contains a pivot table. What I want is a button they can push on the protected sheet that unprotects the sheet, refreshed the pivot table, and then re-protects the sheet. I don't want to button on the unprotected sheet, because if they copy all when moving the data over it might be covered over. But so far my attempts to get a macro to do that has failed. I'd also like it to filter on the pivot table to exclude blank rows but to keep all other values (which will change on a daily basis). Can anyone help me out?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi try this,

You will need to adapt the line for PivotFields to the actual name of the field in your table for which you want to hide blanks. Note, if you have manually hidden them they shouldn't reappear anyway.

This will retain full pivot table functionality after refresh

Code:
Sub UnprotectRefreshProtect()


With ActiveSheet
  .Unprotect
  .PivotTables("PivotTable1").RefreshTable
  .PivotTables("PivotTable1").PivotFields("name").PivotItems("(blank)").Visible = False
  .Protect , _
        AllowUsingPivotTables:=True
End With


End Sub

If you don't want to keep this functionality change as per below.

Code:
Sub UnprotectRefreshProtect()


With ActiveSheet
  .Unprotect
  .PivotTables("PivotTable1").RefreshTable
  .PivotTables("PivotTable1").PivotFields("name").PivotItems("(blank)").Visible = False
  .Protect 
End With


End Sub
 
Upvote 0
or if you have Final macro to run more macros together, just put
Code:
activesheet.unprotect
and on the end of your macro
Code:
activesheet.protect
...if you have password, then use
Code:
activesheet.protect "your password"
and the same on the end with unprotect.
 
Upvote 0
offcourse I ment
Code:
activesheet.unprotect "your password"
at the beginning of code and
Code:
activesheet.protect "your password"
at the end of the code :-) Not the oposite as I described by mistake
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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