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