Find Protected Cells Q

[John]

Is it possible to list a list of protected Cells in a worksheet, without
having to Right Click on each cell?

Thanks

 

[Frank Kabel]

Hi
you could do this with VBA cycle through all cells. How do you want to
output this information and is VBA feasible for you?

 

[John]

I just want to display, listed, on a a blank sheet those say within a
certain range. Its in order to protect cells I want to protect and identify
ones that are not. The default is obviously Protect, but I've worked a good
bit on the sheet so want to be sure

Thanks

 

[Jason Morin]

I think you mean locked. Individual cells can be locked
and unlocked, but protection refers to worksheets,
workbooks, and VBA projects. This macro will list
all "unlocked" cells. Select the range of cells and run
this macro:

Sub ListLocked()

'//Constructive criticism from VBA
'//programmers appreciated

Dim cell As Range
Dim rng As Range
Dim i As Long
Dim wList As Worksheet

Set rng = Intersect(Selection, ActiveSheet.UsedRange)
Set wList = Sheets.Add
wList.Cells(1, 1).Value = "Unlocked Cells"

i = 2
For Each cell In rng
With cell
If .Locked = False Then
wList.Cells(i, 1).Value = .Address(False, False)
i = i + 1
End If
End With
Next

End Sub

 

[Gord Dibben]

John

Would changing background color of locked cells be of any use?

Sub Locked_Cells()
Dim Cell As Range, tempR As Range, rangeToCheck As Range
'first select a range of columns or CRTL + A
'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.Select
tempR.Interior.ColorIndex = 3
End Sub

Gord Dibben Excel MVP

 

[John]

Thanks Jason - exactly what I want

I think you mean locked. Individual cells can be locked
and unlocked, but protection refers to worksheets,
workbooks, and VBA projects. This macro will list
all "unlocked" cells. Select the range of cells and run
this macro:

Sub ListLocked()

'//Constructive criticism from VBA
'//programmers appreciated

Dim cell As Range
Dim rng As Range
Dim i As Long
Dim wList As Worksheet

Set rng = Intersect(Selection, ActiveSheet.UsedRange)
Set wList = Sheets.Add
wList.Cells(1, 1).Value = "Unlocked Cells"

i = 2
For Each cell In rng
With cell
If .Locked = False Then
wList.Cells(i, 1).Value = .Address(False, False)
i = i + 1
End If
End With
Next

End Sub

 

[John]

Thanks Gord, neat piece of code

John

Would changing background color of locked cells be of any use?

Sub Locked_Cells()
Dim Cell As Range, tempR As Range, rangeToCheck As Range
'first select a range of columns or CRTL + A
'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.Select
tempR.Interior.ColorIndex = 3
End Sub

Gord Dibben Excel MVP

 

[Gord Dibben]

John

Not mine....forgot to attribute to Bob Flanagan

http://www.add-ins.com/pages.htm

Thanks Bob.

Gord

Thanks Gord, neat piece of code