protection and changing formulas

[Derrick]

Can i protect a sheet so that users of a spreadsheet can't manually change
the formulas, but a macro can automatically?

i have a validated list that changes certain formulas based on what option
is selected. I want to protect the sheet (apart from certain input cells -
but i know how to do that) so that numbers will be automatically calculated,
no matter what is selected - right now i get 'run-time error 1004' - and the
debugger stops the macro at the 1st line where a formual is changed.

so - macro can change formulas, but not humans. thats the goal. any help?
thanks!!

 

[Dave Peterson]

You can protect the worksheet in code and allow your macro to do lots of things
that the user can't.

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

This may not work with every change your macro wants to do, though.

But if that's the case, you could
unprotect the sheet
do the work
reprotect the sheet

And that should work no matter what you want to do.

 

[Derrick]

So,
this coding will let me do this?

Just making sure my problem is understood, so here goes:
a template, for 'incompetent'(lets say) users, who only know how to insert
dimensions into specific highlighted dimension cells.
so
A3 = 2 (width)
A4 = 3 (height)
A5 = 1(modular width),
A6 = 2.5(Modular height)

in my macro:
if B2 = "Modular height template"
formula in B3 = (Modular height * Width)
if b2 = "Non-Modular template"
b3 = Width*Height

so - user only puts in numbers. the formulas change, and output different
numbers when the user selects a different 'b2' option

currently, i cannot do that. If i lock the B3 cells, so that the chosen
formula won't be lost, it wont let me change it to another one (as per an
option change in b2) with the macro.

OH! another thing.. one option inserts a formula to calculate the number of
vertical members, and leaves horizontal members blank
the 2nd option does the opposite - leaves vertical members blank, and has a
formula for horizontals.
can i set up protection for the cells with the formulas only? ie. turn
on/off the locked cell property for specifically chosen cells so that the end
user can insert the number of vertical/horizontal members into the cells that
are left blank, but not change the number of members in the cells with a
formula.

wow. that got more complicated. sorry!
hopefully you have an idea?

 

[Dave Peterson]

If you can think of the rules, then you should be able to make a macro that does
the work. But my previous response was just how to handle the worksheet
protection. And I don't see anything in your follow-up that would change that
inital response.

On the other hand, if you've limited the number of options for that B2 cell, you
may not need a macro:

=if(b2="Option 1",Option1formulahere,if(b2="option2",Option2formulahere,....

But this kind of nesting won't support too many options (in xl2003 and below).