List sheets’ properties

Tested in Excel 365 (16.8730.2046) 64-bit

Creates a sheet with all sheets names and flags those that are protected or hidden.

 
 
  1. Public Sub ListSheetsProperties()
  2.     Dim i
  3.    
  4.     Sheets.Add Before:=Sheets(1)
  5.     ActiveSheet.Name = "File Info"
  6.    
  7.     For i = 1 To Sheets.Count
  8.         Cells(i + 1, 1).Value = Sheets(i).Name
  9.         If Sheets(i).Visible = False Then Cells(i + 1, 2).Value = "Hidden"
  10.         If Sheets(i).ProtectContents Then Cells(i + 1, 3).Value = "Protected"
  11.     Next i
  12.     Range("A1:C1").Value=Split("Sheet|Visibility|Protectioon","|")
  13. End Sub

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.