Results 1 to 7 of 7

Thread: Locked Cells

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way to check cells in an Excel 2003 workbook to see if they are locked rather than just checking each cell individually?


    Thanks
    Richard

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you select a range of cells (up to and including all the cells in the worksheet) and look at format - cells- protection (tab)

    If the Locked is unchecked, ALL the cells are unlocked
    If the Locked is fully checked, ALL the cells are locked

    If the Locked is "partially checked", some of the cells are unlocked, some are locked and you would have to reduce the selection down until it indicated all or none locked.

    No matter what the indication, at that stage you can fully check or uncheck to make all cells locked or unlocked in the selction

    Steve

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Aheron,

    Since the protection status of cells is immaterial unless the sheet is protected you can check the sheet protection level in VBA as follows:

    Code:
    Sub CheckProtection()
    
       MsgBox "The current Worksheet is Protected: " & ActiveSheet.ProtectContents, _
              vbOKOnly + vbInformation, "Sheet Protection Status"
              
    End Sub
    I would assume (oh watch out for that word) that no one would protect a worksheet after unprotecting all the cells. Thus, this should solve your problem, at least as I interpret what you want to find out.

    Hope this helps.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    As a general rule, if I am creating a spreadsheet that will have both locked and unlocked cells I create a style for the unlocked cells, in addition to setting the protection status I add a coloured fill. I use that style for all the unlocked cells. I can see at a glance what is locked/unlocked and it makes it easier for others to know where the data entry areas are.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That is the issue. The sheet does get Protection set = true. I do have both Locked and unLocked cells on the sheet. As I did not create this, the style was set before I got it. I to do something similar with the fill for unLocked cells.

    I was hoping for a simple way to find all the locked cells, but it seems if I have both on a sheet, I will just have to check each cell individually.


    Thanks to everyone for your feedback.
    Richard

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Richard,

    Ok if I understand you correctly you have a workbook that someone else created and you want to be able to find all the unlocked cells? Give this a try.

    Code:
    Sub IdenUnLockedCells(bSetClear As Boolean, Optional vColor As Variant)
    
    'Usage: To color all unlocked cells background.
    '         IdenUnlockedCells True, color
    '               Where: color is an integer color index code.
    '       Color Codes: 3=Red, 5=Blue, 10=Green
    '       To clear the background on all unlocked cells
    '         IdenUnlockedCells False
    
        Dim rng As Range
        Dim zArea As String
        
       If bSetClear And IsMissing(vColor) Then vColor = 3  'Default to Red
       [A1].Select
       Selection.SpecialCells(xlCellTypeLastCell).Select
       zArea = "A1:" & Selection.Address(xlA1)
       
       For Each rng In Range(zArea)
          If Not rng.Locked Then
            If bSetClear Then
               With Range(rng.Address).Interior
                  .ColorIndex = vColor
                  .Pattern = xlSolid
               End With
            Else
              Range(rng.Address).Interior.ColorIndex = xlNone
            End If
          End If
          
       Next rng
       
    End Sub
    Please Note: As this macro stands it will only scan the Current Region also it will do nothing for a worksheet without any data since data is what is used to define the Current Region.

    I hope this helps.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I will give this a try.

    Thanks for you help
    Richard

Posting Permissions

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