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

[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

[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

"Zadig Galbaras" <[email protected]> wrote in message
news:[email protected]...
> 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
> -----
>
>
>

[Zadig Galbaras]

Hi Paul & Dominic

Worked as a charm.

Thank you to you both!

--

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


"Zadig Galbaras" <[email protected]> skrev i melding
news:[email protected]...
> 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]

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

"Zadig Galbaras" <[email protected]> wrote in message
news:[email protected]...
> Hi Paul & Dominic
>
> Worked as a charm.
>
> Thank you to you both!
>
> --
>
> Regards
> Zadig Galbaras
> A Perturbed Norwegian Agnostic
> -----
>
>
> "Zadig Galbaras" <[email protected]> skrev i melding
> news:[email protected]...
> > 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
> > -----
> >
> >
> >
>
>