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

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" <REMOVE_CAPS_eFGsSFhDG@DDJgEiDGtDsD.DJdRETk> wrote in message
news:u01fLqQaGHA.428@TK2MSFTNGP02.phx.gbl...
> 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
>

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" <normanjones@whereforartthou.com> skrev i en meddelelse
news:uxm21FRaGHA.3612@TK2MSFTNGP03.phx.gbl...
> 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" <REMOVE_CAPS_eFGsSFhDG@DDJgEiDGtDsD.DJdRETk> wrote in message
> news:u01fLqQaGHA.428@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>
>

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" <REMOVE_CAPS_eFGsSFhDG@DDJgEiDGtDsD.DJdRETk> wrote in message
news:eme0R1RaGHA.3736@TK2MSFTNGP04.phx.gbl...
> 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

Hi Norman

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

Ebbe

"Norman Jones" <normanjones@whereforartthou.com> skrev i en meddelelse
news:O%23K0b$RaGHA.4060@TK2MSFTNGP02.phx.gbl...
> 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" <REMOVE_CAPS_eFGsSFhDG@DDJgEiDGtDsD.DJdRETk> wrote in message
> news:eme0R1RaGHA.3736@TK2MSFTNGP04.phx.gbl...
>> 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
>
>

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

--
Tim Williams
Palo Alto, CA


"Ebbe" <REMOVE_CAPS_eFGsSFhDG@DDJgEiDGtDsD.DJdRETk> wrote in message news:%23xsoiwcaGHA.4564@TK2MSFTNGP03.phx.gbl...
> Hi Norman
>
> Thank you for your suggestions
> I can use it with some small changes
>
> Ebbe
>
> "Norman Jones" <normanjones@whereforartthou.com> skrev i en meddelelse
> news:O%23K0b$RaGHA.4060@TK2MSFTNGP02.phx.gbl...
> > 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" <REMOVE_CAPS_eFGsSFhDG@DDJgEiDGtDsD.DJdRETk> wrote in message
> > news:eme0R1RaGHA.3736@TK2MSFTNGP04.phx.gbl...
> >> 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
> >
> >
>
>