+ Reply to Thread
Results 1 to 6 of 6

Overview over lockd cells in a sheet.

  1. #1
    Ebbe
    Guest

    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



  2. #2
    Norman Jones
    Guest

    Re: 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" <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
    >




  3. #3
    Ebbe
    Guest

    Re: 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" <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
    >>

    >
    >




  4. #4
    Norman Jones
    Guest

    Re: 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" <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




  5. #5
    Ebbe
    Guest

    Re: 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" <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

    >
    >




  6. #6
    Tim Williams
    Guest

    Re: Overview over lockd cells in a sheet.

    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

    > >
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1