Count and name number of unprotected worksheets

**tarunpreet** · 10-21-2012, 11:44 AM

Hello All,

For my project, I need some code through which I can count and have a list of unprotected worksheets in a workbook. I tried it but didn't get any sort of result.

I will appreciate your help friends.

**HaHoBe** · 10-21-2012, 11:50 AM

Hi, tarunpreet,

maybe something like

Sub tarunpreet()
Dim ws As Worksheet
Dim strMsg As String

For Each ws In ThisWorkbook.Worksheets
  If Not ws.ProtectContents Then
    strMsg = strMsg & ws.Name & vbCrLf
  End If
Next ws
MsgBox Left(strMsg, Len(strMsg) - 2)
End Sub

Ciao,
Holger

**tarunpreet** · 10-21-2012, 09:11 PM

Is there any way I can get count in message box and list in another worksheet!

Thanks for the help

**HaHoBe** · 10-21-2012, 11:32 PM

Hi, tarunpreet,

change the name of the worksheet to suit, Row 1 is left for the header in Column A:

Sub tarunpreet_2()
Dim ws As Worksheet
Dim lngUnprot As Long

Const cstrSUM As String = "Summary"

With Sheets(cstrSUM)
  .Range("A2:A" & .Rows.Count).ClearContents
  For Each ws In ThisWorkbook.Worksheets
    If Not ws.ProtectContents Then
      .Cells(.Cells(Rows.Count, "A").End(xlUp).Row + 1, "A").Value = ws.Name
      lngUnprot = lngUnprot + 1
    End If
  Next ws
End With
MsgBox "This Workbook contents " & lngUnprot & " worksheets"
End Sub

Ciao,
Holger