Can you lock one cell without protecting the whole sheet?

[imported_unknown]

Is there a way to protect cells containing formulas without protecting the whole sheet?

I'm sick of people messing up the spreadsheet by overwriting the formulas, but they need to be able to save their work.

Thanks.

 

[lmcd5666]

You can protect cells (or whole rows or columns) by highlighting the cells (i.e. data input cells) you DON'T want to protect then click on "Format"->"Cells"->"Protection" tab. Uncheck the "Locked" box. Now protect your worksheet and/or workbooks with a password. Now that the worksheet is protected, the formula cells which were not unchecked will be protected and the user can enter data on these other cells which were unchecked meaning they can be modified. Hope that helps.

Toan

 

[Fryer Tuck]

Yes.

Before locking the sheet, specify if the cell is to be locked. From what you mentioned, you want to select the entire sheet, unlock the sheet, then specify whcih ones will get locked.

Look for the icon of a Lock in the "Format" listing.

F.T.

 

[imported_unknown]

Thank you very much.

 

[kskinne]

As Paddy kindly pointed out to me in a previous post, another way to "protect" an individual cell without using tools-protection, is to use validation. assuming that cell A1 is the cell in question, apply this custom validation to the cell you want to protect:

=len(A1)=""

change the cell reference to suit your own needs. setting this will allow you to leave protection off, but will not allow anyone to change the contents of this one cell.

HTH
kevin

 

[PaddyD]

On 2002-10-02 17:15, kskinne wrote:
As Paddy kindly pointed out to me in a previous post, another way to "protect" an individual cell without using tools-protection, is to use validation. assuming that cell A1 is the cell in question, apply this custom validation to the cell you want to protect:

=len(A1)=""

change the cell reference to suit your own needs. setting this will allow you to leave protection off, but will not allow anyone to change the contents of this one cell.

HTH
kevin

although there's the fairly large caveat that users can still copy / drag items into a cell "protected" by validation. (Just like they can destroy data validation in general by copying / draging a value into the cell).

Another option for protecting just the odd cell or two would be VBA. The following's from Tom Urtis:<pre>


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address<> "$A$1" Then Exit Sub
Application.EnableEvents = False
MsgBox "Hey, leave me alone!",
48, "Sorry, I'm protected."
Application.Undo
Application.EnableEvents = True
End Sub</pre>


see the following for more info:

http://216.92.17.166/board/viewtopic.php?topic=15008&forum=2


Paddy
This message was edited by PaddyD on 2002-10-02 20:02

 

[Paul B]

If you just want to protect cells containing formulas then how abut something like

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.HasFormula Then
MsgBox ("Formula in cell do not remove or change!")
Else
End If
End Sub

 

[GeorgeB]

Try this
Select "Data"
Select "Validation"
On the "Settings" tab
Click the "Allow" drop box
Select "Text length"
In the "Data" drop box
Select "less than"
In the "Maximum" box put 0 (zero)
Click OK
Voila

As above copy/drag will defeat this.
This message was edited by GeorgeB on 2002-10-02 22:50

 

[Tanner_2004]

This worked great for me. Thank you!

 

[dmt32]

a vba solution that may do what you want:

Dave

Sub ProtectFormulas()
    
    With ActiveSheet
        .Unprotect Password:="" '<<add password if required
        .Cells.Locked = False
        .UsedRange.SpecialCells(xlCellTypeFormulas).Locked = True
        .Protect Password:=""
        
        'allow selection of unlocked cells only
        .EnableSelection = xlUnlockedCells
    End With

End Sub