I'm in the process of validating an excel spreadsheet. The question has been
raised about how we prove that the cells containing the calculations within
the spreadsheet are actually protected. It would be possible to go thru each
individual calculation cell and attempt to type something into it, thereby
causing the error message box to appear, then screenprint it. However, the
spreadsheet has something in the region of 150 calculation cells, so this is
an impractical solution. What I need is a method by which Excel can be made
to list all of the protected cells within the spreadsheet, but this must be
driven by Excel itself, rather than something which amounts to me just
creating a range/list of cells myself. Can you help?

Thanks in advance

Jo

Good afternoon Jo

I have an add-in available that has a routine that will select ALL
unlocked cells in a given range. This add-in is free of charge to
anyone requesting it on .

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=379023

Jo

Select protected cells.............

Sub Locked_Cells()
Dim Cell As Range, tempR As Range, rangeToCheck As Range
Cells.Select
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If 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 Locked cells " & _
"in the selected range."
End
End If
'''
tempR.Select
'''
End Sub

To create a list on a new sheet substitute tempR.Select with the following.

Worksheets.Add
Set Rng = Range("A1")
For Each Cell In tempR
Rng.Offset(i, 0).Value = Cell.Address
i = i + 1
Next Cell


Gord Dibben Excel MVP


On Tue, 14 Jun 2005 07:55:05 -0700, "Jo" wrote:

I'm in the process of validating an excel spreadsheet. The question has been
raised about how we prove that the cells containing the calculations within
the spreadsheet are actually protected. It would be possible to go thru each
individual calculation cell and attempt to type something into it, thereby
causing the error message box to appear, then screenprint it. However, the
spreadsheet has something in the region of 150 calculation cells, so this is
an impractical solution. What I need is a method by which Excel can be made
to list all of the protected cells within the spreadsheet, but this must be
driven by Excel itself, rather than something which amounts to me just
creating a range/list of cells myself. Can you help?

Thanks in advance

Jo