Cell Protection

G

Guest

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

Kenji

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
 
G

Gord Dibben

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
 
D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top