View Locked Cells?

  • Thread starter Thread starter Nick Jenkins
  • Start date Start date
N

Nick Jenkins

Is there a way to show which cells have been locked without having to check
each cell? I have a very large spreadsheet with multiple worksheets. There
are only a few areas within each worksheet that are not locked so the user
can input information. The rest of each worksheet is locked and shows various
calculations. I need to make sure that the locekd cells are indeed 'locked'.
 
Nick said:
Is there a way to show which cells have been locked without having to check
each cell? I have a very large spreadsheet with multiple worksheets. There
are only a few areas within each worksheet that are not locked so the user
can input information. The rest of each worksheet is locked and shows various
calculations. I need to make sure that the locked cells are indeed 'locked'.
Click to expand...

You can use conditional formatting.

=CELL("protect",A1)
 
As long as the sheet is protected you can use the TAB key to browse
unlocked cells.

PS I usually change the fill color of unlocked cells so that user can
tell which ones are open (cuts down on frustration on their part as
well).
 
If you have only a few unlocked cells it may be easier to indentify them
rather than all the locked ones.

Sub UnLockedCells()
Dim wk As Worksheet
Dim cl As Range
For Each wk In Worksheets
For Each cl In wk.UsedRange
If cl.Locked = False
cl.Interior.ColorIndex = 6
End If
Next
Next
End Sub

If you really want to identify the locked cells change the false to true.


Gord Dibben MS Excel MVP
 
Back
Top