How to freeze a cell on a specific condition

aashwinjain

New Member
Joined
Aug 11, 2015
Messages
7
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

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

How is A1 changing? Via formula or manually?
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
OK, then you need to use a Calculate event:

Code:
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

New Member
Joined
Aug 11, 2015
Messages
7
OK, then you need to use a Calculate event:

Code:
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

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Aug 11, 2015
Messages
7
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

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Aug 11, 2015
Messages
7
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

New Member
Joined
Aug 11, 2015
Messages
7
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
 

Forum statistics

Threads
1,081,524
Messages
5,359,269
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top