Cell Protection
 

Is there a tool I can run or maybe even a macro that would show me what cells
are locked or unlocked. I have a big worksheet that has a big mix of
protected and unprotected cells, so I am trying to find a short cut to see
which cells are which, without checking formating or entering data into each
cell.

Yes, there is a built in function for you that'll return true or false
depending on if it's locked or not.

Here's a quick function I wrote for you
Function isLocked(ByVal row As Integer, ByVal column As Integer) As Boolean
isLocked = Cells(row, column).Locked
End Function


This is a test sub routine for you to see how it works.
Sub test()
If isLocked(1, 1) Then
MsgBox "Row 1, Col 1 is locked"
Else
MsgBox "Row 1, Col 1 is NOT locked"
End If
End Sub

Kenjiro Yagi




"biglautz" wrote in message
...
Is there a tool I can run or maybe even a macro that would show me what
cells
are locked or unlocked. I have a big worksheet that has a big mix of
protected and unprotected cells, so I am trying to find a short cut to see
which cells are which, without checking formating or entering data into
each
cell.

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 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
'select, shade or clear qualifying cells
tempR.Select
'tempR.Interior.ColorIndex = 3 'red
'tempR.ClearContents
End Sub


Gord Dibben Excel MVP

On Mon, 23 May 2005 12:38:02 -0700, "biglautz"
wrote:

Is there a tool I can run or maybe even a macro that would show me what cells
are locked or unlocked. I have a big worksheet that has a big mix of
protected and unprotected cells, so I am trying to find a short cut to see
which cells are which, without checking formating or entering data into each
cell.

Are you using Format|Conditional formatting?

If yes, then ignore this response.

If no, you could select your range (whole sheet???) and use:
format|conditional formatting
formula is: =CELL("protect",A1)
(with a1 the active cell.)


If you wanted to toggle this on/off, you could put some flag in a cell (I used
$a$1) and check that:

=AND(CELL("protect",A1),$A$1="Show")

If I put Show in $a$1, then all the locked cells will have that conditional
formatting. If I want to turn it off, I just clear the contents of A1.



biglautz wrote:

Is there a tool I can run or maybe even a macro that would show me what cells
are locked or unlocked. I have a big worksheet that has a big mix of
protected and unprotected cells, so I am trying to find a short cut to see
which cells are which, without checking formating or entering data into each
cell.

--

Dave Peterson