backing up formulas in sheet coding?

[Derrick]

I have a calculation sheet, where i want to make sure that users dont
accidentally delete the formulas necessary to complete the calculations.
- i've tried protecting the cells, by unlocking certain input cells and
locking the rest, but that for some reason won't allow my 'hide rows, and
insert formulas depending on what is selected from a validated list' macro to
run properly.

my idea now is to have a startup macro, where if the sheet is copied or
whatever, the formulas will be saved in code and inserted into the correct
cells if needed
- or if the formula is accidentally deleted it will re-appear.
where would i insert that coding?

thanks for helping!

Also, if you have a better idea, or a way to fix the 1st problem, let me know!

 

[Luke M]

You could do something like the following. This will make sure your formula
is there, and if not, replace it.

On sheet you have formula, right click, and go to view code. Paste this in,
modifying appropriately.

Private Sub Worksheet_Activate()
'Change cell references & formulas accordingly
Range("A2").Formula = "=SUM(B1:B4)"
Range("A3").Formula = "=AVERAGE(B1:B4)"
End Sub

 

[Gord Dibben]

Derrick

A better way could be to add a couple of lines to your macro(s) which
unprotect the sheet, run the code then re-protect the sheet.

Sub Macro1()
Activesheet.unprotect password:="mypass"

code runs here to hide rows or insert formulas

Activesheet.protect password:="mypass"


Gord Dibben MS Excel MVP