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

No, it isn't a bug - it is the designed behavior. Settings such as this and
scrollarea, enableautofilter, enableselection and enableoutline are
temporary and are not retain when the workbook is closed.

You need to use the workbook_Open event or other appropriate event to set
them so they are in effect when the workbook is opened.

--
Regards,
Tom Ogilvy


"Dag Johansen" wrote in 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

Hi Tom,

thanks a lot for quick response. I have a follow-up
question then...

Not all sheets should necessarily be protected. The user
can choose to protect the active sheet on a custom menu
much like the normal protection, is prompted for an
(optional) password and then the Protect method is called.

My question is: In order to use the Workbook_Open
workaround, how would you recommend I persist the settings
for the sheets, so in the event handler I can make the
call to protect if and only if the user did indeed protect
it? (I wouldn't like to have to capture and handle events
such as renaming, reordering, deleting or inserting sheets
in the workbook.)

Happy coding,

Dag
-----Original Message-----
No, it isn't a bug - it is the designed behavior.
Settings such as this and
scrollarea, enableautofilter, enableselection and
enableoutline are
temporary and are not retain when the workbook is closed.

You need to use the workbook_Open event or other
appropriate event to set
them so they are in effect when the workbook is opened.

--
Regards,
Tom Ogilvy


"Dag Johansen" wrote in 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


.

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
...
Hi Tom,

thanks a lot for quick response. I have a follow-up
question then...

Not all sheets should necessarily be protected. The user
can choose to protect the active sheet on a custom menu
much like the normal protection, is prompted for an
(optional) password and then the Protect method is called.

My question is: In order to use the Workbook_Open
workaround, how would you recommend I persist the settings
for the sheets, so in the event handler I can make the
call to protect if and only if the user did indeed protect
it? (I wouldn't like to have to capture and handle events
such as renaming, reordering, deleting or inserting sheets
in the workbook.)

Happy coding,

Dag
-----Original Message-----
No, it isn't a bug - it is the designed behavior.
Settings such as this and
scrollarea, enableautofilter, enableselection and
enableoutline are
temporary and are not retain when the workbook is closed.

You need to use the workbook_Open event or other
appropriate event to set
them so they are in effect when the workbook is opened.

--
Regards,
Tom Ogilvy


"Dag Johansen" wrote in 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


.