Marking unlocked cells in active worksheet

[Zadig Galbaras]

Hi u all!

I have a rather large spreadsheet taking care of our soccer betting club,
and I need to clear the contains of every cell that is not locked when the
spreadsheet is locked.

Is this possible?

I tried this simple function in the EDIT/GOTO...but it seems not to do the
job.
May be I use it wrong, but I couldn't make it work. I tried all
possibilities there I guess.



--

Regards
Zadig Galbaras
A Perturbed Norwegian Agnostic
-----

 

[Paul B]

Zadig, here is one way with a macro

Sub Delete_Unlocked_Cells()
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


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[dominicb]

Good evening Zadig Galbaras

The code below will select all unlocked cells in a given range, making
it very easy for you to clear - just press delete. However this will
not work with the spreadsheet protected. An easy way around this would
be to make the first line unprotect the sheet and the last line protect
it again. I don't know how you have protected your sheet (ie password,
other settings) so I can only give a little guidance on these commands,
but at their most basic would be:

ActiveSheet.Unprotect

and

ActiveSheet.Protect

Sub Macro1()
Count = 0
On Error Resume Next
For Each rng In Selection
If rng.Locked = False Then
Count = Count + 1
If Count = 1 Then Set Unlocked = rng
If Count <> 1 Then Set Unlocked = Union(Unlocked, rng)
End If
Next rng
Unlocked.Select
End Sub

HTH

DominicB

 

[Zadig Galbaras]

Hi Paul & Dominic

Worked as a charm.

Thank you to you both!

 

[Paul B]

Your Welcome

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

Hi Paul & Dominic

Worked as a charm.

Thank you to you both!