Forum Discussion

excelnoob298's avatar
excelnoob298
Copper Contributor
Mar 04, 2020

Need some help with VBA

Hi

I have a excel file where i have combined pivot table and CSV. That pivot table is always updating. I have found the code for the background update but What Im struggling with and want some help in, is to find a VBA code which can run on a protected sheet. When i protect the sheet, the VBA code which is for automatically updating pivot table dont work anymore. Does anyone know a code i can use here? Where in protected sheet the updating pivot table code will still run?

Thanks

excel
Formulas and Functions
Macros and VBA

6 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Iron Contributor
    Mar 04, 2020

    excelnoob298 -

    Did you use the method described at https://www.excelcampus.com/vba/refresh-pivot-tables-automatically/?

     

    I was able to accomplish as:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets("Pivot").Unprotect
    ThisWorkbook.RefreshAll
    Worksheets("Pivot").Protect
End Sub


    Workheets("Pivot") is Protected and cells cannot be selected.

    • excelnoob298's avatar
      excelnoob298
      Copper Contributor
      Mar 05, 2020

      ChrisMendoza Yeah i tried it before. first problem was that code is running in loop so my excel file crash. the second problem is that for the pivot to update the sheet has to open and then close. If i have 30-50 sheets to work on then that would be too tiring to type in the same password always. If in the meeting the file need a update i have to type in for all those sheets. 
      What i want is a code which will run in the background and just update the pivot tables i have in this 30-50 sheets without typing the password and not the whole file which crash. 

      I need this code because i have formulas written on every sheet. when i copy paste it or want to share the file. other people can see the formulas. Thats why the file is closed and i want to update the pivot table in closed/protected sheets. 

      Thanks 

      • ChrisMendoza's avatar
        ChrisMendoza
        Iron Contributor
        Mar 05, 2020

        excelnoob298 -

        Why would you have to type the password each time on multiple sheets? You input the Password as an argument like:

        Worksheets(currentSheetName).Unprotect Password:="SuperSecretPassword"
.
.
.
Worksheets(currentSheetName).Protect Password:="SuperSecretPassword", userinterfaceonly:=True

         

        You should also probably be using as a wrapper:

        Application.ScreenUpdating = False
.
.
.
Application.ScreenUpdating = True

         

        I would also recommend that rather than Worksheet_Change using something like Workbook_BeforeClose or Workbook_BeforeSave. In these ways, you're not always running the procedure.

Resources