Showing which cells are locked

[smithista]

Is there a way to show which cells in a worksheet are locked?

Thanks
Stephen

 

[Jim Thomlinson]

There is no facility to select all of the unlocked cells or such. What you
can do is to put the lock icon onto one of your toolbars...

Tools -> Customize -> Commands (tab) -> Format -> Lock Cell (icon about 3/4
the way down the list) -> drag it to your toolbar...

 

[Gary''s Student]

First unprotect the worksheet and then run this macro:

Sub islocked()
Set rLocked = Nothing
For Each r In ActiveSheet.UsedRange
If r.Locked Then
If rLocked Is Nothing Then
Set rLocked = r
Else
Set rLocked = Union(r, rLocked)
End If
End If
Next
If rLocked Is Nothing Then
Else
rLocked.Select
End If
End Sub

 

[Dave Peterson]

Are you using Format|Conditional Formatting for anything?

If no, then how about using it to show the locked/unlocked cells?

Select your range (ctrl-A for all the cells).
Format|Conditional formatting
formula is: =CELL("protect",A1)

(Use the activecell's address instead of A1.)

 

[Gord Dibben]

Sub Locked_Cells()
'Bob Flanagan source of code
Dim Cell As Range, tempR As Range, rangeToCheck As Range
'check each cell in the selection
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Cell.Locked Then
If tempR Is Nothing Then
'initialize tempR with the first qualifying cell
Set tempR = Cell
Else
'add additional cells to tempR
Set tempR = Union(tempR, Cell)
End If
End If
Next Cell
'display message and stop if no cells found
If tempR Is Nothing Then
MsgBox "There are no Locked cells " & _
"in the selected range."
End
End If
'select qualifying cells
tempR.Interior.ColorIndex = 3
End Sub


Gord Dibben MS Excel MVP