Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 . |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Skipping and protected cells | Excel Discussion (Misc queries) | |||
Find all cells with a number and mulitply | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
Protected cells -automatically format to a different color | Excel Discussion (Misc queries) | |||
How do you delete one cell from a range of protected cells | Excel Worksheet Functions |