Forum Discussion
Need some help with VBA
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.
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
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 SubChrisMendoza the code didnt work for me, but anyway Thanks for the help. 🙂