Finding Locked Cells ... How

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Excel 2000 ...

I have a spread sheet (my own) where I wish to know which
cells I have Locked/unlocked ... How do I do this? ... I
tried "GO TO" "Special", but do not see what I recognize
to be an applicable category ... Thanks ... Kha ...
 
Ken

You could protect the sheet and then press the tab key. Excel will cycle
through 'unlocked' cells one at a time though.

Running this code with an area selected will turn all the 'unlocked' cells
red

Sub HighlightUnlocked()
Dim myCell As Range
For Each myCell In Selection
If Not myCell.Locked Then
myCell.Interior.Color = RGB(255, 0, 0)
End If
Next myCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Select all the cells you want to "test", and use format/conditional
formatting. Change "Cell Value Is" to "Formula Is", and assuming A1 is the
active cell, enter =IsLocked(A1). Then click the Format button and choose a
pattern for the locked cells.
Noe enter this VBA function:
Function Islocked(rg As Range) As Boolean
Application.Volatile True
Islocked = rg.Locked
End Function
Put calculation to automatic and you're all set.
 
Here is one way:

Select your range (I'm assuming A1 is the activecell) and
do
Format|conditional Formatting
Use Formula is
and a formula of:
=CELL("protect",A1)

Pick an appropriate shade. All cells that are locked
will be highlighted in this shade.
 
Select all the cells you want to "test", and use format/conditional
formatting. Change "Cell Value Is" to "Formula Is", and assuming A1 is the
active cell, enter =IsLocked(A1). Then click the Format button and choose a
pattern for the locked cells.
Noe enter this VBA function:
Function Islocked(rg As Range) As Boolean
Application.Volatile True
Islocked = rg.Locked
End Function
Put calculation to automatic and you're all set.
Click to expand...
...

Um, =CELL("Protect",A1) ? Or is wheel reinvention part of the package?
 
...
...
Running this code with an area selected will turn all the 'unlocked' cells
red

Sub HighlightUnlocked()
Dim myCell As Range
For Each myCell In Selection
If Not myCell.Locked Then
myCell.Interior.Color = RGB(255, 0, 0)
End If
Next myCell
End Sub
Click to expand...
...

But no way to undo this easily. Better just to select those cells. Maybe
overkill, but I believe the following is the quickest approach.


Sub SelUnprot()
Dim ows As Worksheet, tws As Worksheet, icm As Long

If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub

On Error GoTo CleanUp

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
icm = Application.Calculation
Application.Calculation = xlCalculationManual

Set ows = ActiveSheet
Set tws = ActiveWorkbook.Sheets.Add
Worksheets(Array(ows.Name, tws.Name)).Select
ows.Activate
ows.UsedRange.Select
tws.Select
Selection.FormulaR1C1 = "=CELL(""Protect"",'" & ows.Name & "'!RC)"
Selection.Value = Selection.Value
Selection.Replace what:="1", Replacement:=""
Worksheets(Array(ows.Name, tws.Name)).Select
ows.Activate
ows.UsedRange.Select
tws.Activate
Selection.SpecialCells(Type:=xlCellTypeConstants).Select
tws.Select
tws.Delete

CleanUp:
Application.Calculation = icm
If icm <> xlCalculationManual Then Application.Calculate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
 
Back
Top