+ 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
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    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)

Similar Threads

  1. [SOLVED] Refresh Pivot Table Connected to Protected Sheet
    By tryingtoexcelatexcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-25-2017, 10:42 PM
  2. Refresh Pivot Table in protected sheet
    By bgrish3 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2016, 02:05 PM
  3. Automatic refresh pivot table on protected sheet
    By wrybel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-08-2014, 09:44 AM
  4. How to refresh Pivot table when sheet is protected?
    By vjharry in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-17-2013, 12:09 AM
  5. Replies: 1
    Last Post: 09-18-2012, 04:10 PM
  6. Macro to Allow Refresh of a Pivot Table in a Protected Sheet
    By gavin0311 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-07-2011, 02:32 PM
  7. Pivot table refresh in a protected sheet
    By JoelMessing in forum Excel General
    Replies: 5
    Last Post: 05-04-2009, 02:56 PM

Bookmarks

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