Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel on XP: Password Protection | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
protection on excel forms | Excel Worksheet Functions | |||
Protection in Excel via email | Excel Worksheet Functions |