Can you lock one cell without protecting the whole sheet?

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

lmcd5666

New Member
Joined
Sep 5, 2002
Messages
20
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

Board Regular
Joined
Mar 20, 2002
Messages
64
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.
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Well-known Member
Joined
Feb 15, 2002
Messages
575

ADVERTISEMENT

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

Board Regular
Joined
Feb 16, 2002
Messages
239
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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,841
Office Version
  1. 2019
Platform
  1. Windows
a vba solution that may do what you want:

Dave

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

Forum statistics

Threads
1,144,223
Messages
5,723,120
Members
422,478
Latest member
Rovan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top