freezing a cell in a formula

April 1, 2010 at 17:16:45
Specs: Windows Vista
How do I enter a formula in excel to freeze certain cells depending on what I choose from a different drop down box. For example if I choose "no" from the drop down box I want certain cells frozen so nobody can enter into them but if I choose "yes" from the box, I do not want the cells frozen. Is this possible?

See More: freezing a cell in a formula

Report •


#1
April 1, 2010 at 17:56:19
You can't do it with a formula but you can do it with a WorksheetChange Macro.

First, you need to know that you cannot Protect specific cells, you can only Protect an entire sheet.

However, you can Lock and Unlock specific cells so that once you Protect the sheet the Locked cells can not be changed.

To accomplish what you want try this:

1 - Select the entire sheet by clicking in the box above the Row numbers.
2 - Use Format...Cells...Protection tab
3 - Uncheck the Locked box. Click OK
4 - Select the cells that you want to Protect
5 - Use Format...Cells...Protection tab
6 - Check the Locked box. Click OK

Now, when you Protect the sheet, only the cells that you have Locked will be Protected.

7 - Right Click the sheet tab and choose View Code
8 - Paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Assumes your Drop Down is in B1
 If Target.Address = "$B$1" Then
'Protect the sheet if B1 is "yes"
  If Target = "yes" Then ActiveSheet.Protect Else _
'Unprotect the sheet if B1 is "no"
  If Target = "no" Then ActiveSheet.Unprotect
 End If
End Sub

Note: Unless you also Protect the code, any user could delete it and defeat what you are trying to do.

Note (the sequel): When the sheet is Unprotected the user could Unlock the cells and the Protection would have no effect next time you put "no" in B1.


Report •
Related Solutions


Ask Question