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

Anonymous
2018-09-24T15:24:10+00:00

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.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2018-09-24T15:38:05+00:00

    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.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2018-09-24T15:45:34+00:00

    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!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2018-09-24T16:22:58+00:00

    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.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2018-09-24T16:54:20+00:00

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

    Was this answer helpful?

    0 comments
  5. Anonymous
    2018-09-24T16:55:06+00:00

    How are you protecting it?

    Was this answer helpful?

    0 comments