Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a locked/protected sheet
If I want to add/edit data onto a locked and password
protected sheet (to which I already know the password), should my VBA code first "unprotect" the sheet, add data, then "protect" the sheet again?? Or can my VBA code simply write data onto the protected sheet?? (bypassing the whole lock and unlock procedure) It seems to me that both methods might work....if that is so, would any method be better than the other for any reason??? thankx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a locked/protected sheet
You can allow macros to do lots of things that the user can't if you protect
your worksheet in code. For instance, you can allow your code to do autofiltering with code like this: (saved from a previous post) If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) But there are somethings that even your code can't do if the worksheet is protected this way. You'll need to unprotect and reprotect to make things work. (I don't remember any specific examples, but after a few tests, you may find them!) The one thing I would try to do is to leave the sheet unprotected for as little time as possible: wks.unprotect do something wks.protect I don't want something to happen in the middle of my code (an error or even ctrl-break by the user) that allows them to have access to what they shouldn't. ps. Look in VBA's help for EnableCancelKey for ways to stop that ctrl-break. Robert Crandal wrote: If I want to add/edit data onto a locked and password protected sheet (to which I already know the password), should my VBA code first "unprotect" the sheet, add data, then "protect" the sheet again?? Or can my VBA code simply write data onto the protected sheet?? (bypassing the whole lock and unlock procedure) It seems to me that both methods might work....if that is so, would any method be better than the other for any reason??? thankx -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a locked/protected sheet
Yes, you will need to unprotect the sheet, change data, then protect the
sheet. Hope this helps! If so, let me know, click "YES" below. Sub YourMacro() With Sheets("Sheet1") .Unprotect "Your Password" ' change data code here .Protect "Your Password" End With End Sub -- Cheers, Ryan "Robert Crandal" wrote: If I want to add/edit data onto a locked and password protected sheet (to which I already know the password), should my VBA code first "unprotect" the sheet, add data, then "protect" the sheet again?? Or can my VBA code simply write data onto the protected sheet?? (bypassing the whole lock and unlock procedure) It seems to me that both methods might work....if that is so, would any method be better than the other for any reason??? thankx . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying a locked/protected sheet
I use this in Private Sub Workbook_Open() procedure in thisworkbook:
Worksheets("datafile").Protect Password:="xxx", Contents:=True, Scenarios:=False, UserInterfaceOnly:=True, AllowFiltering:=True It allows me to protect the sheet against user changes but still write to the sheet in code without any protect/unprotect code. I don't claim that this is the most efficient code, just that it works for me. "Robert Crandal" wrote in message ... If I want to add/edit data onto a locked and password protected sheet (to which I already know the password), should my VBA code first "unprotect" the sheet, add data, then "protect" the sheet again?? Or can my VBA code simply write data onto the protected sheet?? (bypassing the whole lock and unlock procedure) It seems to me that both methods might work....if that is so, would any method be better than the other for any reason??? thankx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allowing editing locked cells in a protected sheet in excel | Excel Worksheet Functions | |||
Relacing default Excel message box when user attempts to change locked cell on protected sheet. | Excel Programming | |||
Able to overwrite locked cell in protected sheet | Excel Discussion (Misc queries) | |||
my sheet is locked and protected, i want to insert row with autofo | Excel Programming | |||
Still can select locked cells in protected sheet | New Users to Excel |