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 Jenkins wrote:
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'.

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

On Sep 3, 11:14*am, Glenn wrote:
Nick Jenkins wrote:
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'.

You can use conditional formatting.

=CELL("protect",A1)

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

On Wed, 3 Sep 2008 07:21:01 -0700, Nick Jenkins
wrote:

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