Overview over lockd cells in a sheet.

[Ebbe]

Hi

I am working with a sheet in witch som registration is entered and then some
formels ar calculating on the entered data.

My concern is that I have unprotected cells, that should be protected and
protected cells, that should be unprotected

Is it possible to highligt all protected/unprotected cells in a sheet?
The formel control function varns me if a cell with a formel is not
protected (locked).
But blank cells and cells with prompt text can be unprotected without
varnings.

Ebbe

 

[Norman Jones]

Hi Ebbe,

Try:

'=============>>
Public Sub ToggleLockedCellsColor()
Dim rCell As Range
Dim iColor As Long
Dim bNoColor As Boolean
Dim bLocked As Boolean
Dim res As String

res = InputBox("Enter the sheet protection password")

iColor = 6 '<<==== Yellow - Change to taste
bLocked = False

On Error Resume Next
ActiveSheet.Unprotect res
If Err.Number <> 0 Then
MsgBox "Password not recognised"
Exit Sub
End If

For Each rCell In ActiveSheet.UsedRange.Cells
With rCell
If .Locked Then
bNoColor = rCell.Interior.ColorIndex = xlNone
bLocked = True
Exit For
End If
End With
Next rCell

If Not bLocked Then
MsgBox Prompt:="No locked cells found!", _
Buttons:=vbInformation, _
Title:="Locked Cells"
End If

For Each rCell In ActiveSheet.UsedRange.Cells
With rCell
If .Locked Then
.Interior.ColorIndex = IIf(bNoColor, iColor, xlNone)
End If
End With
Next

ActiveSheet.Protect res

End Sub
'<<=============

 

[Ebbe]

Hi Norman

This code looks very interesting.

I was looking for a tool-botton or so that marks the locked/unlocked
celles, but not permanently changes the appearance of the cells.
But if this is the only way, I will turn this code into something I can use.

Ebbe

 

[Norman Jones]

Hi Ebbe,

I was looking for a tool-botton

The suggested code can be assigned to a toolbar button.

... but not permanently changes the appearance of the cells.

The suggested code operates to toggle the colour of the locked cells: if the
cells are not coloured, it colours them; if, conversely, the cells are
coloured, the code removes the colouring.

 

[Ebbe]

Hi Norman

Thank you for your suggestions
I can use it with some small changes

Ebbe

 

[Tim Williams]

see this thread also
http://groups.google.com/group/micr...thor:williams&rnum=109&hl=en#7a74ce59b25ec91e