Using a Refresh Button for a Pivot Table in a protected sheet

Question

Trying to use a refresh button for a pivot table, but I need to protect the worksheet. Currently, when I protect the worksheet, the button comes back as a run-time error 1004. The button works fine when the sheet is unprotected.

Answer 1

You should use the below code. Data sheet you can do onetime protection through Review tab > Protect sheet. VBA code will not be needed for that.

Replace abc with the password of your sheet.

Sub Button5_Click()
    ActiveSheet.Unprotect Password:="abc"
    ActiveSheet.PivotTables(2).PivotCache.Refresh
    ActiveSheet.Protect Password:="abc"
End Sub

Answer 2

I added that to my code and I'm still getting the error when I protect the sheet

Answer 3

This page should help you:

https://docs.microsoft.com/en-us/office/vba/api...

If you have a password, you'll need to add it.

Answer 4

I do have VBA code in it

Sub Button5_Click()

    ActiveSheet.PivotTables(2).PivotCache.Refresh

End Sub

what would I need to add to it? Thanks!

Answer 5

Do you have VBA code in the button? You'll need to add to it to unprotect the sheet, then refresh and then protect it afterwards.