Freeze cells in time!

[macke05]

Hello,

My question is this: Is there a way to have the values within a set of cells (which have been generated by formulas which use numbers which change often) "locked in" once a certain value is entered (like B12 = 1). So if this task were to be accomplished, when B12 = 1 certain selected cells will not change value again (from what they were when that 1 was entered) despite the formulas linked to them demanding they change.

Thanks!

 

[Yongle]

Not with Excel - "once a formula always a formula"

But can use VBA to achieve this quite easily if value in B12 is amended manually

Right click on sheet tab \ View Code \ Paste this code into the window on the right

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range: Set cel = Range("B12")
    If Not Intersect(cel, Target) Is Nothing And cel =  1 Then
            Range("E2:E15").Value = Range("E2:E15").Value
    End If
End Sub

{Alt}{F11} takes you back from VBA to Excel

When value in B12 changes to 1, whatever is in E2:E15 become "frozen in time" (all formulas in the range are replaced by the cell value)

 

[macke05]

So, I altered your code to say:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range: Set cel = Range("N175")
    If Not Intersect(cel, Target) Is Nothing And cel = 1 Then
            Range("T11:T12").Value = Range("T11:T12").Value
    End If
End Sub

Where N175 is the cell in which 1 is typed in (actually I put an "x" in another cell and a formula types 1 in it) and the range I want "frozen" is T11:T12. But when I changed a value T11 and T12 still changes.

 

[Yongle]

in that case use the other cel as the condition, like this
(replacing N999 with address of other cell)

    Dim cel As Range: Set cel = Range("[COLOR=#ff0000]N999[/COLOR]")
    If Not Intersect(cel, Target) Is Nothing And cel = "x" Then

 

[macke05]

It worked!

Thanks!

 

[Yongle]

Thanks for the feedack