delete all but protected cells

  • Thread starter Thread starter Kingtriotn
  • Start date Start date
K

Kingtriotn

Hello,
I am trying to put together a macro to delete all but the protected
cells. Nothing I try seems to work. Has anyone done this before? It
is easy for me to turn protection on and off with a macro so shouldnt
it also be easy to delete the content of all non protected cells as
well?
Thanks in advance
Kingtriton
 
King

Sub UnLocked_Cells()
Dim cell As Range, tempR As Range, rangeToCheck As Range
'rotate through all cells in the selection
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If Not cell.Locked Then
'add to tempR if unprotected
If tempR Is Nothing Then
'do it this way for the first unprotected cell
Set tempR = cell
Else
'add subsequent cells this way.
Set tempR = Union(tempR, cell)
End If
End If
Next cell
'do this upon completion of the For..Next checking
If tempR Is Nothing Then
MsgBox "There are no UnLocked cells in " & _
"the selected range."
End
End If
'select qualifying cells
tempR.Select
Selection.ClearContents
End Sub

Gord Dibben Excel MVP
 
That clears the contents of all unlocked cells but only if the sheet is
not protected. This is where I keep running into walls, why wont it
clear the contents of the unlocked cells when the sheet is protected?
Thanks again,
Kingtriton
 
Kingtriton,
why wont it clear the contents of the unlocked cells when the sheet is
Click to expand...
protected?
The simple answer is because the sheet is protected.

Two options.......
If the sheet is manually password protected, unprotect it before you
run the code and reprotect it afterwards.

or...................

Use this code (modify to suit) in the Workbook_Open event:

Worksheets("yoursheetname") Protect Password:="yourpassword", _
UserInterfaceOnly:=True

The above will protect the worksheet but allow it to be modified via code.
It has to be set each time the workbook opens. The "protect" will stick
on close/reopen but the "UserInterfaceOnly" won't.

John
 
I dont understand, if the cells are unlocked and the sheet is protected,
you can still manipulate the unlocked cells, only the locked cells are
protected when the sheet is protected. I will be distributing this
worksheet to a bevy of computer illeterate people who work for me and I
need it to be SIMPLE for them to use. Is there no way to clear all of
the unprotected cells at once. I could just record a macro and holding
control, select every cell that I want to delete, then hit delete, stop
recording macro and then link it to a button. I want to avoid this
because it makes the sheet move around when you run the sub. Even if I
delete all of the scroll lines in the sub, the sheet still appears to
have a seizure.
Thanks,
Kingtriton
 
Kingtriton,
Is there no way to clear all of
the unprotected cells at once.
I dont understand, if the cells are unlocked and the sheet is protected,
you can still manipulate the unlocked cells, only the locked cells are
protected when the sheet is protected.
Click to expand...
Yes, you can manipulate the unlocked cells but you can't change the
properties of those cells.
Example:
Take a sheet and unlock one cell.
Protect the sheet and then select "Format"
Note that the "Cells" option is grayed out.
The "Format" option is worksheet dependent.
Excel hasn't evaluated what cell you're in at this point.
It only knows that the sheet is protected and you can't choose
that particular option.
Is there no way to clear all of
the unprotected cells at once.
Click to expand...
Gord's code will do this with my suggestions to either protect
the sheet via code (with the UserInterface option) or Unprotect
the sheet before you run Gord's code (and reprotect afterwards).
I want to avoid this because it makes the sheet move around
when you run the sub
Click to expand...
Application.ScreenUpdating = False
at the beginning of your code will cure this.

John
 
King

Macro works for me on a protected sheet.

Hit CRTL + A to select all cells then run it.

Clears the contents of all "unlocked" cells.

Gord Dibben Excel MVP
 
Tested in XL 97 and XL 2002

Gord

King

Macro works for me on a protected sheet.

Hit CRTL + A to select all cells then run it.

Clears the contents of all "unlocked" cells.

Gord Dibben Excel MVP
Click to expand...
 
Back
Top