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?

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?

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?

in no way, shape or form should they be able to edit the calculations
accidentally, is what im saying.

"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?

Lets take one step back...
1 - I assume you have locked the cells with the formulas?
2 - I assume you have unlocked the cells where the user is expected to enter
data?

If this is the case then when the sheet is proctected the formulas can not
be modified but the data entry cells can be edited.

In order for code to modify the formulas the sheet needs to be unprotected.
So if your code unprotects the sheet, modifies the formula and re-protects
the sheet then everything should work out.

FYI i would not be modifiying formulas with code. There are easier
solutions. Assuming that there are 7 or less different formulas to be applied
an If formula in XL can read the validation value and perform the proper
calculation. If there are more than 7 unique calculations then calculate each
value in a seperate hidden cell and use a Vlookup, HLookup or better yet
Index/match formula to return the proper result to a visible cell.
--
HTH...

Jim Thomlinson


"Derrick" wrote:

in no way, shape or form should they be able to edit the calculations
accidentally, is what im saying.

"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?

Ohhh ok. i see now. Had thought that you meant to manually unprotect the
sheets, and re-protect them. looking down again, i see that its written into
the code.

the reason for me putting the formulas into the code is because i dont want
the extra cells used... it might be easier, but it's more hidden in the code.
As well, im using the code to hide and show rows, so I update the formulas
at the same time... the code gets to be long ...

thanks for the help!


"Jim Thomlinson" wrote:

Lets take one step back...
1 - I assume you have locked the cells with the formulas?
2 - I assume you have unlocked the cells where the user is expected to enter
data?

If this is the case then when the sheet is proctected the formulas can not
be modified but the data entry cells can be edited.

In order for code to modify the formulas the sheet needs to be unprotected.
So if your code unprotects the sheet, modifies the formula and re-protects
the sheet then everything should work out.

FYI i would not be modifiying formulas with code. There are easier
solutions. Assuming that there are 7 or less different formulas to be applied
an If formula in XL can read the validation value and perform the proper
calculation. If there are more than 7 unique calculations then calculate each
value in a seperate hidden cell and use a Vlookup, HLookup or better yet
Index/match formula to return the proper result to a visible cell.
--
HTH...

Jim Thomlinson


"Derrick" wrote:

in no way, shape or form should they be able to edit the calculations
accidentally, is what im saying.

"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?

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?