You can test each sheet to see if it is protected.

for each sh in thisworkbook.worksheets
if sh.protectContents then
sh.Activate
sh.protect UserInterfaceOnly:=True
End if
Next

In xl2000 and earlier, you can apply UserInterfaceOnly without knowing the
password. I understand you need to supply the password in xl2002 (and
assume xl2003). I am not sure how you intend to do that other than prompt
the user .

--
Regards,
Tom Ogilvy
"Dag Johansen" wrote in message
...
I just wanted to add that I run a Win2k/Excel 2k system;
my modules need to work with Excel 2000 and higher.

Sincerely,

Dag
-----Original Message-----
Hi,

I want to allow users to protect a sheet not for security
reasons but rather to avoid unintentional overwrites. But
VBA should still be allowed to modify the sheet (used to
refresh some data in a spreadsheet while protecting the
criteria from unintentional modification).

I call WorkSheet.Protect(pwd, UserInterfaceOnly:=True)
and
it works like a dream - for the remainder of the session.
I save, close Excel and start a new instance, load the
saved workbook and... now the sheet has "normal"
protection and VBA is NOT allowed to make modifications.

Does anyone know if this is a bug in Excel and/or how to
overcome the problem?

Help greatly appreciated! Regards,

Dag Johansen