none
Excel VBA to list all cells protection RRS feed

  • Question

  • Hi All,

    I am trying to write a code to get list of all cells protection (in activesheet usedrange) in Test sheet. Below is the example of Test sheet that i want.

    Sheet Name                              Range                    Protection

    Sheet1                                       A1                             Locked

    Sheet1                                       B1                             Unlocked

    Sheet1                                       C1                              locked

    Sheet2                                       A1                             Locked

    Sheet2                                       B1                             Unlocked

    Sheet2                                       C1                              locked

    Thanks,

    Zaveri

                           

    Wednesday, August 27, 2014 8:22 PM

Answers

  • Hi,

    According to your description, you want to check all the cells in the used range of some sheets to get their protection information and list the result into the Sheet "Test".

    If I understand correctly, you just need to loop through all the sheets (excluding "Test") in the workbook to get all the cells of used range. Here is a sample for your reference.

    Sub ListCells()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim cel As Range
    
    Set wb = ActiveWorkbook
    
    For Each ws In wb.Worksheets
        If ws.Name <> "Test" Then
            For Each cel In ws.UsedRange
               'list every cell to sheet Test
            Next
        End If
    Next
    End Sub

    After getting the cell, we can use Range.Locked property to check whether the cell is locked, then write the sheet name with Range.Worksheet.Name property and the cell name with Range.Address Property into the specific line of Sheet "Test" as followed.

    Dim test As Worksheet
    Set test = wb.Sheets("Test")
    
    For Each ws In wb.Worksheets
        If ws.Name <> "Test" Then
            For Each cel In ws.UsedRange
                'list every cell to sheet Test
                i = test.Range("A" & test.Rows.Count).End(xlUp).Row
    
                test.Range("A" & i + 1).Value = cel.Worksheet.Name
                test.Range("B" & i + 1).Value = cel.Address(False, False)
    
                If cel.Locked And cel.Worksheet.ProtectContents Then
                test.Range("C" & i + 1).Value = "Locked"
                Else
                test.Range("C" & i + 1).Value = "Unlocked"
                End If
    
            Next
        End If
    Next


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by zaveri cc Thursday, August 28, 2014 2:09 PM
    Thursday, August 28, 2014 8:18 AM
    Moderator
  • Hi,

    >>I want to loop only 10 sheets which are listed in sheet "TEST2" from A2 to A11. <<

    You just need to loop to get value from cell A2 to A11 of Sheet "Test2", and then use the value as the sheet name to access to the specific sheet, instead of looping through all the worksheets.

    Sub test()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Integer
    
    Dim test2 As Worksheet
    Dim sheetName As String
    
    Set wb = ActiveWorkbook
    
    Set test2 = wb.Sheets("Test2")
    
    For i = 2 To 11
        sheetName = Trim(test2.Range("A" & i).Value)
        If sheetName <> "" Then
            Set ws = wb.Sheets(sheetName)
            'If ws.Name <> "Test" Then
                For Each cel In ws.UsedRange
                  'list every cell to sheet "Test"
                Next
            'End If
        End If
    Next i
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 29, 2014 1:59 AM
    Moderator

All replies

  • There are a number of options here.

    http://www.extendoffice.com/documents/excel/1053-excel-identify-select-locked-cells.html


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, August 27, 2014 9:05 PM
  • Hi,

    According to your description, you want to check all the cells in the used range of some sheets to get their protection information and list the result into the Sheet "Test".

    If I understand correctly, you just need to loop through all the sheets (excluding "Test") in the workbook to get all the cells of used range. Here is a sample for your reference.

    Sub ListCells()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim cel As Range
    
    Set wb = ActiveWorkbook
    
    For Each ws In wb.Worksheets
        If ws.Name <> "Test" Then
            For Each cel In ws.UsedRange
               'list every cell to sheet Test
            Next
        End If
    Next
    End Sub

    After getting the cell, we can use Range.Locked property to check whether the cell is locked, then write the sheet name with Range.Worksheet.Name property and the cell name with Range.Address Property into the specific line of Sheet "Test" as followed.

    Dim test As Worksheet
    Set test = wb.Sheets("Test")
    
    For Each ws In wb.Worksheets
        If ws.Name <> "Test" Then
            For Each cel In ws.UsedRange
                'list every cell to sheet Test
                i = test.Range("A" & test.Rows.Count).End(xlUp).Row
    
                test.Range("A" & i + 1).Value = cel.Worksheet.Name
                test.Range("B" & i + 1).Value = cel.Address(False, False)
    
                If cel.Locked And cel.Worksheet.ProtectContents Then
                test.Range("C" & i + 1).Value = "Locked"
                Else
                test.Range("C" & i + 1).Value = "Unlocked"
                End If
    
            Next
        End If
    Next


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by zaveri cc Thursday, August 28, 2014 2:09 PM
    Thursday, August 28, 2014 8:18 AM
    Moderator
  • Sub Test()
      Dim Ws As Worksheet
      Dim Data, i As Long
      Dim C As New Collection
      Dim R As Range
      For Each Ws In Worksheets
        ReDim Data(1 To Ws.UsedRange.Cells.Count, 1 To 3)
        i = 0
        For Each R In Ws.UsedRange.Cells
          i = i + 1
          Data(i, 1) = Ws.Name
          Data(i, 2) = R.Address(0, 0)
          Data(i, 3) = IIf(R.Locked, "Locked", "Unlocked")
        Next
        C.Add Data
      Next
      Worksheets.Add
      Range("A1:C1") = Array("Sheet Name", "Range", "Protection")
      i = 2
      For Each Data In C
        Cells(i, 1).Resize(UBound(Data), UBound(Data, 2)) = Data
        i = i + UBound(Data)
      Next
    End Sub


    Thursday, August 28, 2014 10:05 AM
  • Thanks. Code works fine. I want to loop only 10 sheets which are listed in sheet "TEST2" from A2 to A11. How shall i change the code?

    Thanks,

    Zaveri

    Thursday, August 28, 2014 1:40 PM
  • Hi,

    >>I want to loop only 10 sheets which are listed in sheet "TEST2" from A2 to A11. <<

    You just need to loop to get value from cell A2 to A11 of Sheet "Test2", and then use the value as the sheet name to access to the specific sheet, instead of looping through all the worksheets.

    Sub test()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Integer
    
    Dim test2 As Worksheet
    Dim sheetName As String
    
    Set wb = ActiveWorkbook
    
    Set test2 = wb.Sheets("Test2")
    
    For i = 2 To 11
        sheetName = Trim(test2.Range("A" & i).Value)
        If sheetName <> "" Then
            Set ws = wb.Sheets(sheetName)
            'If ws.Name <> "Test" Then
                For Each cel In ws.UsedRange
                  'list every cell to sheet "Test"
                Next
            'End If
        End If
    Next i
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 29, 2014 1:59 AM
    Moderator
  • Thanks it worked great.

    Friday, August 29, 2014 3:44 PM