Modifying a locked/protected sheet

[Robert Crandal]

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]

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.

 

[Ryan H]

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

 

[Project Mangler]

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.