+ Reply to Thread
Results 1 to 1 of 1

VBA to refresh pivot table on protected sheet but keep correct settings

  1. #1
    Forum Contributor
    Join Date
    MS-Off Ver
    Excel 2013

    VBA to refresh pivot table on protected sheet but keep correct settings

    Oops I see what I did wrong. I fixed it now.

    I have a pivot table that refreshes when users click a button. I just protected the sheet so users can't override formulas, but they are allowed to modify anything else such as add in rows or format cells a different color.

    Once I protected the sheet, I found out pivot tables won't refresh on protected sheets. I found some VBA code to make the Pivot table refresh by first unprotecting the sheet, refreshing, and then reprotecting the sheet. The problem is when the sheet is re-protected it defaults to only only users to click in cells and nothing else (they can't format, add hyperlinks etc.)

    I recorded a macro of me selecting all the things the user can do, and then inserted that code, but it still prevents them from formatting cells or anything else after the pivot table refereshes.

    Please Login or Register  to view this content.

    It does refresh the pivot table, and it does re-protect the sheet, but then users can not make formatting changes or add hyperlinks, which they may want to do. How can I make this possible?
    Last edited by dsrt16; 03-08-2018 at 06:51 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1