Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Lock and Unlock a sheet
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, -- Drew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Lock and Unlock a sheet
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). "Drew" wrote: 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, -- Drew |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Lock and Unlock a sheet
Drew;526102 Wrote: 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, -- Drew There are two things tyou can do: 1. Unprotect the sheet before updating and re-protect after with the likes of: Sheets("Sheets1").Unprotect 'update code here Sheets("Sheets1").Protect of if passwords are 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 code to alter it: Sheets("Sheets1").Protect UserInterfaceOnly:=Truewith passwords:Sheets("Sheets1").Protect "secretpassword", UserInterfaceOnly:=TrueYou can run these lines even if the 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 the file, then re-open it and the protection will be full protection, necessitating the execution of that line (Sheets("Sheets1").Protect "secretpassword", UserInterfaceOnly:=True) at least once somewhere in the code to enable code to update the sheet. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144455 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Lock and Unlock a sheet
Private Sub Worksheet_Activate()
Me.Unprotect Password:="drowssap" 'do your stuff code Me.Protect Password:="drowssap" End Sub Gord Dibben MS Excel MVP On Thu, 15 Oct 2009 09:23:45 -0700, Drew wrote: 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to unlock and lock cells in password protected sheet | Excel Discussion (Misc queries) | |||
I FORGOT MY PASSWORD AND THE EXCEL SHEET IS LOCK HOW CAN I UNLOCK | Excel Discussion (Misc queries) | |||
i want to lock a tab in excel, and unlock in another sheet | Excel Programming | |||
Unlock/Lock VBProject through code? | Excel Programming | |||
Unlock/Lock VBProject through code? | Excel Programming |