Overview over lockd cells in a sheet.
 

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

Overview over lockd cells in a sheet.
 

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
'<<=============


---
Regards,
Norman


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

Overview over lockd cells in a sheet.
 

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" skrev i en meddelelse
...
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
'<<=============


---
Regards,
Norman


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

Overview over lockd cells in a sheet.
 

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.

---
Regards,
Norman



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

Overview over lockd cells in a sheet.
 

Hi Norman

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

Ebbe

"Norman Jones" skrev i en meddelelse
...
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.

---
Regards,
Norman



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

Overview over lockd cells in a sheet.
 

see this thread also
http://groups.google.com/group/micro...74ce59b25ec91e

--
Tim Williams
Palo Alto, CA


"Ebbe" wrote in message ...
Hi Norman

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

Ebbe

"Norman Jones" skrev i en meddelelse
...
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.

---
Regards,
Norman



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