Locking cells with formulas

[ArtySin]

I've got a s/sheet where here are some cells with the RAND function in them.
If a user enters a number in cell j2 the sheet runs and all is OK. However
the results change if a user enters any thing in any other cell and presses
enter. Is there anyway to prevent this so that the cells change only if J2 is
changed? Cell locking does not do this unfortunately.
Cheers

 

[Wigi]

You can protect all cells except J2.

 

[ArtySin]

No that doesn't work. If you unlock the cells that you want to change by
using the Ctrl+1 font dialog box and then protect the w/sheet, when you enter
data in an unlocked cell it still changes the data in the locked cells.

 

[Gord Dibben]

This seems to work.....but nothing will caclulate if J2 is not changed so may be
a side effect you don't want.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculation = xlManual
If Target.Address = "$J$2" Then
Application.Calculate
End If
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Oooops!

Slight flaw in previous post.

This might be better

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculation = xlManual
If Target.Address = "$J$2" Then
Application.Calculate
End If
End Sub


Gord