Excel sheet protection
 

i have an excel sheet that contains a pivot table that i would like to
protect to avoid selecting locked cells but when i do so i can not refresh
the data of the pivot table!!!
What shall i do

Excel sheet protection
 

you could create a macro like this that will uprotect then refresh then
protect sheet
without a password
Sub refresh()
ActiveSheet.Unprotect
ActiveWorkbook.RefreshAll
ActiveSheet.Protect
End Sub

"Yazan Barakat" wrote:

i have an excel sheet that contains a pivot table that i would like to
protect to avoid selecting locked cells but when i do so i can not refresh
the data of the pivot table!!!
What shall i do

Excel sheet protection
 

This way you could use a password
Sub refreshwithpassword()
Application.ScreenUpdating = False
Sheet1.Unprotect "Password"
ActiveWorkbook.RefreshAll
Sheet1.Protect "Password"
Application.ScreenUpdating = True
End Sub


"Yazan Barakat" wrote:

i have an excel sheet that contains a pivot table that i would like to
protect to avoid selecting locked cells but when i do so i can not refresh
the data of the pivot table!!!
What shall i do

Excel sheet protection
 

Thank you Mr. Mike the solution is partially valid, when i run the macro it
refreshs correctly but when it reprotects it changes the user allowable
options so he will not be able to filter the results.


"Mike" wrote:

you could create a macro like this that will uprotect then refresh then
protect sheet
without a password
Sub refresh()
ActiveSheet.Unprotect
ActiveWorkbook.RefreshAll
ActiveSheet.Protect
End Sub

"Yazan Barakat" wrote:

i have an excel sheet that contains a pivot table that i would like to
protect to avoid selecting locked cells but when i do so i can not refresh
the data of the pivot table!!!
What shall i do

Excel sheet protection
 

gives run time error if a password already exists, if not it creats a new
password that i do not know

"Mike" wrote:

This way you could use a password
Sub refreshwithpassword()
Application.ScreenUpdating = False
Sheet1.Unprotect "Password"
ActiveWorkbook.RefreshAll
Sheet1.Protect "Password"
Application.ScreenUpdating = True
End Sub


"Yazan Barakat" wrote:

i have an excel sheet that contains a pivot table that i would like to
protect to avoid selecting locked cells but when i do so i can not refresh
the data of the pivot table!!!
What shall i do

Excel sheet protection
 

Sheet1.Unprotect "Password"
Sheet1.Protect "Password" 'Password is the Password you can change to what you
want


"Yazan Barakat" wrote:

gives run time error if a password already exists, if not it creats a new
password that i do not know

"Mike" wrote:

This way you could use a password
Sub refreshwithpassword()
Application.ScreenUpdating = False
Sheet1.Unprotect "Password"
ActiveWorkbook.RefreshAll
Sheet1.Protect "Password"
Application.ScreenUpdating = True
End Sub


"Yazan Barakat" wrote:

i have an excel sheet that contains a pivot table that i would like to
protect to avoid selecting locked cells but when i do so i can not refresh
the data of the pivot table!!!
What shall i do