Lock formula to cell

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

I want a formula to lock to a cell so that when I add or delete a column the
formula will always remain with say column H

I know if the sheet is Password protected this will prevent this happening
but that is no use when I have to unprotect the sheet to delete a column.

Anyone got thoughts on this?

regards
Pat
 
Hi Pat
a little bit complicated and not fully tested but try the following
code. Put it in your worksheet module (you have to change the
sheetname)

Private Sub Worksheet_Change(ByVal Target As Range)
Const fixed_name = "lock_formula"
Const refer_str = "=Sheet1!$H$1"
Dim rng As Range
Dim save_formula

If IsError(Evaluate(fixed_name)) Then
ActiveWorkbook.Names.Add Name:=CStr(fixed_name),
RefersTo:=CStr(refer_str)
Else
If CStr(ActiveWorkbook.Names(CStr(fixed_name)).RefersTo) <>
CStr(refer_str) Then
On Error GoTo CleanUp
Application.EnableEvents = False
Set rng = Evaluate(fixed_name)
save_formula = rng.Formula
rng.Value = ""
Set rng = Range(CStr(refer_str))
rng.Formula = save_formula
ActiveWorkbook.Names(CStr(fixed_name)).RefersTo =
CStr(refer_str)
Else
'do nothing
End If
End If

CleanUp:
Application.EnableEvents = True
End Sub

HTH
Frank
 
Frank

That seems to work nicely. I have changed the range to:

Const refer_str = "=Sheet1!$H$1:$H$800"

If I want to extend the range to cover another group of cells would it
just a matter of altering the code to:

Const refer_str = "=Sheet1!$H$1:$H$800&Sheet1$R$1:$R$800 "

Pat
 
Back
Top