Locking cells with formulas
 

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

Locking cells with formulas
 

You can protect all cells except J2.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"ArtySin" wrote:

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

Locking cells with formulas
 

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.

"Wigi" wrote:

You can protect all cells except J2.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"ArtySin" wrote:

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

Locking cells with formulas
 

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

On Sat, 19 Jul 2008 15:57:01 -0700, ArtySin
wrote:

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.

"Wigi" wrote:

You can protect all cells except J2.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"ArtySin" wrote:

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

Locking cells with formulas
 

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

On Sat, 19 Jul 2008 16:27:48 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Sat, 19 Jul 2008 15:57:01 -0700, ArtySin
wrote:

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.

"Wigi" wrote:

You can protect all cells except J2.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"ArtySin" wrote:

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