Excel protection
 

I want to protect some cells in a sheet so that I can just select and
blank the whole sheet while keeping the formulas in some cells intact.
The problem is that, having unlocked the editable cells and protected
the worksheet, when I then select the entire sheet it won't let me just
blank the unlocked cells - it tells me that some cells are protected.
Can anyone please help.

If you want to keep ALL formulas and clear everything else......

Leave the sheet unprotected then select all cells and F5SpecialConstants.
Check or uncheck what you want to clear then OK.

With those cells selected, EditClearContents will leave formulas intact.

If just some specific cells you could leave the sheet protected and use a
macro to select only those that are unlocked and clear contents of those.

Sub UnLocked_Cells()
Dim Cell As Range, tempR As Range, rangeToCheck As Range
Cells.Select
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Not Cell.Locked Then
If tempR Is Nothing Then
Set tempR = Cell
Else
Set tempR = Union(tempR, Cell)
End If
End If
Next Cell
If tempR Is Nothing Then
MsgBox "There are no UnLocked cells in " & _
"the selected range."
End
End If
tempR.Select
tempR.ClearContents
End Sub


Gord Dibben Excel MVP

On 17 Feb 2005 07:07:25 -0800, wrote:

I want to protect some cells in a sheet so that I can just select and
blank the whole sheet while keeping the formulas in some cells intact.
The problem is that, having unlocked the editable cells and protected
the worksheet, when I then select the entire sheet it won't let me just
blank the unlocked cells - it tells me that some cells are protected.
Can anyone please help.

Another macro for clearing contents of unlocked cells.

Little less code.

Sub DeleteUnlockedCells()
Dim rngeCell As Range
Application.ScreenUpdating = False
For Each rngeCell In ActiveSheet.UsedRange.Cells
If rngeCell.Locked = False Then rngeCell.ClearContents
Next
Application.ScreenUpdating = True
End Sub


Gord

On 17 Feb 2005 07:07:25 -0800, wrote:

I want to protect some cells in a sheet so that I can just select and
blank the whole sheet while keeping the formulas in some cells intact.
The problem is that, having unlocked the editable cells and protected
the worksheet, when I then select the entire sheet it won't let me just
blank the unlocked cells - it tells me that some cells are protected.
Can anyone please help.

wrote in message
ups.com...
I want to protect some cells in a sheet so that I can just select and
blank the whole sheet while keeping the formulas in some cells intact.
The problem is that, having unlocked the editable cells and protected
the worksheet, when I then select the entire sheet it won't let me just
blank the unlocked cells - it tells me that some cells are protected.
Can anyone please help.


Have you thought about recording a macro?