Turn protection Off... <run code>... turn protection back on

[DFS]

I'm building a fairly locked-down Excel system (no sorting, no insert/delete
rows, lots of locked cells, etc) that will find its way into the hands of
end-users, and I'm finding most operations are requiring me to turn
protection off, then run the routine, then turn protection back on.

Is there a better way?

 

[Dave Peterson]

Depends on what you're doing.

Somethings code can do that the user can't -- if you protect the worksheet in
code.

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

I used the Autpen procedure (in a General module) because excel doesn't
remember this setting. It has to be run each time the workbook is opened--or at
least before you can expect your code to do something to that protected
worksheet.

But there are some things that even the code can't do and you have to use what
you're using now.

 

[DFS]

Depends on what you're doing.

Somethings code can do that the user can't -- if you protect the
worksheet in code.

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

I used the Autpen procedure (in a General module) because excel
doesn't remember this setting. It has to be run each time the
workbook is opened--or at least before you can expect your code to do
something to that protected worksheet.

But there are some things that even the code can't do and you have to
use what you're using now.

Thanks Dave. I'll look into that setting.

Occasionally I'm using "hidden" cells (white font that can't be seen) that
say On or Off, but I hate obscure/kludges like that.

 

[Trevithick]

I use something similar to Dave Peterson, but it only unlocks the worksheet
when a piece of code needs to update the sheet, and then it protects it again
after making the update.

Write procedures that do nothing but protect and unprotect your current
worksheet. Then I simply call those procedures from other procedures that
are updating the worksheet after some calculations.

This keeps end users from making a mess of things.