Code to Lock and Unlock a sheet

[Drew]

I'm sure that this is very basic code, but I am a newby to VBA. Currently I
have some code that is running that will update my pivot table when I click
on the tab where the pivot table resides (Sales). However, when I protect
this sheet, the code fails and I get an error. Can someone help me with the
code that I need to unprotect the sheet prior to the code that runs the pivot
table update and protect the sheet once the code is done running.

Thanks,

 

[B Lynn B]

Using unprotect and reprotect creates risk of sheets being left unprotected
in the event of runtime errors unless you take considerable care in your
error handling.

Simpler solution is to protect with the property UserInterfaceOnly set to
true. e.g...

ActiveSheet.protect "password", UserInterfaceOnly:=True

This allows any running VBA code to make changes while the user cannot do so
directly. Items of note when using this method - it is not retained after
the workbook is closed, so you may want to include it in the workbook open
event. And it's occasionally a little quirky about not allowing certain
kinds of changes, (Copy and paste a range from unprotected source to
protected sheet - go figure).

 

[p45cal]

I'm sure that this is very basic code, but I am a newby to VBA.
Currently I
have some code that is running that will update my pivot table when
click
on the tab where the pivot table resides (Sales). However, when
protect
this sheet, the code fails and I get an error. Can someone help m
with the
code that I need to unprotect the sheet prior to the code that runs th
pivot
table update and protect the sheet once the code is done running.

Thanks,

There are two things tyou can do:
1. Unprotect the sheet before updating and re-protect after with th
likes of:
Sheets("Sheets1").Unprotect
'update code here
Sheets("Sheets1").Protect
of if passwords ar
involved:Sheets("Sheets1").Unprotect "secretpassword"
'update code here
Sheets("Sheets1").Protect "secretpassword"

2.Protect the sheet only as far as the user is concerned, allowing cod
to alter it:
Sheets("Sheets1").Protec
UserInterfaceOnly:=Truewit
passwords:Sheets("Sheets1").Protect "secretpassword"
UserInterfaceOnly:=TrueYou can run these lines even if th
sheet is already protected. Be aware that according to the help file
this last only lasts as long as the file is open. Save and Close th
file, then re-open it and the protection will be full protection
necessitating the execution of that line (Sheets("Sheets1").Protec
"secretpassword", UserInterfaceOnly:=True) at least once somewhere i
the code to enable code to update the sheet

 

[Gord Dibben]

Private Sub Worksheet_Activate()
Me.Unprotect Password:="drowssap"
'do your stuff code
Me.Protect Password:="drowssap"
End Sub


Gord Dibben MS Excel MVP