Find Protected Cells Q
 

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

Thanks

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?

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
Is it possible to list a list of protected Cells in a worksheet,
without
having to Right Click on each cell?

Thanks

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


"Frank Kabel" wrote in message
...
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?

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
Is it possible to list a list of protected Cells in a worksheet,
without
having to Right Click on each cell?

Thanks

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

---
HTH
Jason
Atlanta, GA

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

Thanks


.

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

On Tue, 7 Dec 2004 14:15:42 -0000, "John" wrote:

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


"Frank Kabel" wrote in message
...
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?

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
Is it possible to list a list of protected Cells in a worksheet,
without
having to Right Click on each cell?

Thanks

Thanks Jason - exactly what I want


"Jason Morin" wrote in message
...
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

---
HTH
Jason
Atlanta, GA

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

Thanks


.

Thanks Gord, neat piece of code


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Tue, 7 Dec 2004 14:15:42 -0000, "John"
wrote:

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


"Frank Kabel" wrote in message
...
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?

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
Is it possible to list a list of protected Cells in a worksheet,
without
having to Right Click on each cell?

Thanks

John

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

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

Thanks Bob.

Gord


On Thu, 9 Dec 2004 21:47:10 -0000, "John" wrote:

Thanks Gord, neat piece of code


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
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

On Tue, 7 Dec 2004 14:15:42 -0000, "John"
wrote:

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


"Frank Kabel" wrote in message
...
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?

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
Is it possible to list a list of protected Cells in a worksheet,
without
having to Right Click on each cell?

Thanks