How to freeze a cell on a specific condition

[aashwinjain]

Hi there !!

Could someone please help me with this.

Say i have two cell

A1 and A4

If a specific condtion is met in A1 { Say if value of a1 =3 } then A4 should freeze and should not exept any value.

Help will be much appreciated. Thank you

 

[Smitty]

Welcome to the Board!

How is A1 changing? Via formula or manually?

 

[aashwinjain]

Welcome to the Board!

How is A1 changing? Via formula or manually?

A1 is changing via formula

 

[Smitty]

OK, then you need to use a Calculate event:

Private Sub Worksheet_Calculate()
  If Range("A1").Value = 3 then
     ActiveSheet.Unprotect
       Range("A4").Locked = True
     ActiveSheet.Protect
  Else     
     ActiveSheet.Unprotect
       Range("A4").Locked = False
     ActiveSheet.Protect
  End If

End Sub

 

[aashwinjain]

OK, then you need to use a Calculate event:

Private Sub Worksheet_Calculate()
  If Range("A1").Value = 3 then
     ActiveSheet.Unprotect
       Range("A4").Locked = True
     ActiveSheet.Protect
  Else     
     ActiveSheet.Unprotect
       Range("A4").Locked = False
     ActiveSheet.Protect
  End If

End Sub

Hey Smitty : Greta.. thanx for the quick response

Couple of queries : Would be great if you could sort them as well :

• Is it possible to make the value a variable instead of fixing it to 3 { Value of a1 could be variable depending on the out come of the formula : So say if A1's outcome based on formula is either 3,6,9 then we freeze the cell else we dont }
• How to we implement this on cell A2. How do we add this macro for cell a2

Thanx in advance

 

[Smitty]

If Range("A1").Value = 3 Or If Range("A1").Value = 6 Or If Range("A1").Value = 9 Then

Any more than that and you probably want to use a Select Case structure.

As for adding A2, will it be affecting another cell or A4 as well?

 

[aashwinjain]

Thanx for updating :

the point which is not clear is : How do i implement this.

Say i want to freeze A4 depending on the value of A1. Now that you have given me the code. Should i create a macro { If yes then what next }. Please guide though this

 

[Smitty]

Right-click on the sheet tab where you want this to take place and select View Code. Paste the code in the new window that opens, then Alt+Q to exit back to Excel. The code will fire automatically when the sheet calculates.

 

[aashwinjain]

Right-click on the sheet tab where you want this to take place and select View Code. Paste the code in the new window that opens, then Alt+Q to exit back to Excel. The code will fire automatically when the sheet calculates.

Thanx Smitty : WOrks perfectly well :

Just the last set of questions :

• Can i customize the error message that i get when i try to enter data in freezed cell
• also when the cell is freezed based on the condition can i change the cell color to grey

Thank you and really great full for the help

 

[aashwinjain]

Also once the condition in A1 is met { Say a1=3} and a4 gets freezed out then we are unable to change the vaule of any cell. We get the message saying the file is protected. Please unprotect