If you write your protect/unprotect code properly using event code, the deed
will be done without users knowing it is happening and will not leave the
sheet unprotected except when the code is running.

No user-fidding allowed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C1:C10")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="justme"
Select Case Target.Value
Case "module width"
'doodah doodah
Case "module height"
'camptown races
End Select
endit:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

Lock the project for viewing so's users cannot see the code.


Gord Dibben MS Excel MVP


On Fri, 24 Jul 2009 10:57:01 -0700, Derrick
wrote:

not quite.

the goal is to allow other employees to not have to deal with sort of
syntax/runtime/calc errors. I don't want them to have to fiddle around with
the settings in order for them to get things to work. They should be able
to open the file and punch in the numbers...
again, is there coding that overrides this protection setting.. or is there
a combo of settings i can use?

"Jim Thomlinson" wrote:

Unprotect and reprotect the sheet...

Sheets("Sheet1").Unprotect
'your code
Sheets("Sheet1").Protect

--
HTH...

Jim Thomlinson


"Derrick" wrote:

Hi.
I have a sheet in which i want to protect the formulas being used for
calculations.
In this sheet however, there is VBA coding/automatically running macros that
changes the formulas depending on what option is checked in a validated list.

This makes excel unhappy, and i get a run-time error... i'm assuming it is
because the protection is preventing formula changes.

So the question is: what can i do to allow the code to change the formulas,
while not letting the user change them manually.

ie.
if "module width" is selected
Area (in cell a1) = Module width x Total height
if "module height" is selected
Area (in cell a1) = total width x module height
etc.

any thoughts?