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 ...
excel
Formulas and Functions
Macros and VBA
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.

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

    ChrisMendoza  I new to VBA coding so what i understood and wrote the code based on your comment. if you could check It looks like this:

     

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Worksheets(Sheet1).Unprotect Password:="password"

    Me.PivotTable(1).RefreshTable

    Application.ScreenUpdating = False

    Application.ScreenUpdating = True

    Worksheets(Sheet1).Protect Password:="password", userinterfaceonly:=True


    End Sub

     

    now the next thing i didnt understand is when i wrote this code nothing happened. in protected or unprotcted sheet this code didnt run. what am i doing wrong here?

    And do i have to always open the VBA application for the code to work? 

     

    When i wrote the code for update only it was always updating the pivot when i input a new data without opening VBA. but now i want to protect the sheet and run the code in background without opening VBA after writing the new code. 

    Thanks

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

      excelnoob298 -

      Try this:

      Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Application.ScreenUpdating = False
    
    Worksheets("Pivot").Unprotect Password:="SuperSecretPassword"
    
    ThisWorkbook.RefreshAll
    
    Worksheets("Pivot").Protect Password:="SuperSecretPassword", userinterfaceonly:=True
    
    Application.ScreenUpdating = True
End Sub

       

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

        ChrisMendoza the code didnt work for me, but anyway Thanks for the help. 🙂 

Resources