Workbook Worksheet If, Then, ElseIf not working to get worksheet visible property

[ddander54]

I have cobbled some code together from multiple places that creates a new workbook, then adds an Index sheet and loops thru a selected folder to get the Workbook name, Worksheet name and then the Worksheet.visible property. All of it is working fine except the Worksheet.visible property. It displays that a sheet is visible when it is hidden. I have tried If, Then, ElseIf, End If and multiple If, Then, End If and neither seems to work for me. Thoughts?
Results: File WorkMix.xlsx Sheet3 should read Hidden, The rest are Visible, which is correct.

C:\MyExcelFiles\Test2\*.xls*
Book1.xlsx
	Main	Visible
	Data	Visible
Data.xlsx
	Data	Visible
History.xlsx
	Historical	Visible
Source.xlsm
	Sheet1	Visible
Summary.xlsm
	Summary	Visible
WorkMix.xlsx
	Actuals	Visible
	Sheet1	Visible
	Sheet2	Visible
	Sheet3	Visible <<<< This should be "Hidden"
---END OF FOLDER---

'Option Explicit

'https://www.mrexcel.com/board/threads/loop-through-every-file-in-folder-and-list-all-sheet-names.663583/

Sub FolderCrawler()
FileType = "*.xls*"     'The file type to search for
FilePath = "C:\MyExcelFiles\Test2\"   'The folder to search
OutputRow = 2   'The first row of the active sheet to start writing to

Dim ws As Worksheet, NewSheet As Worksheet, wb As Workbook, I As Integer

Set wb = Workbooks.Add
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.name = "Index"

OutputRow = 2   'The first row of the active sheet to start writing to
wb.ActiveSheet.Range("A" & OutputRow) = FilePath & FileType
OutputRow = OutputRow + 1
Curr_File = Dir(FilePath & FileType)
Do Until Curr_File = ""
    Set FldrWkbk = Workbooks.Open(FilePath & Curr_File, False, True)
    wb.ActiveSheet.Range("A" & OutputRow) = Curr_File
    wb.ActiveSheet.Range("B" & OutputRow).ClearContents   'Clear any previous values
    OutputRow = OutputRow + 1
   
    For Each sht In FldrWkbk.Sheets
        wb.ActiveSheet.Range("B" & OutputRow) = sht.name
        If wb.ActiveSheet.Visible = xlSheetVisible Then
        wb.ActiveSheet.Range("C" & OutputRow) = "Visible"
        End If
        If wb.ActiveSheet.Visible = xlSheetHidden Then
        wb.ActiveSheet.Range("C" & OutputRow) = "Hidden"
        End If
        If wb.ActiveSheet.Visible = xlSheetVeryHidden Then
        wb.ActiveSheet.Range("C" & OutputRow) = "Very Hidden"
        End If
        'wb.ActiveSheet.Range("C" & OutputRow) = sht.Visible
        wb.ActiveSheet.Range("A" & OutputRow).ClearContents 'Clear any previous values
        OutputRow = OutputRow + 1
    Next sht
 
    FldrWkbk.Close SaveChanges:=False
    Curr_File = Dir
Loop
Set FldrWkbk = Nothing
wb.ActiveSheet.Range("A" & OutputRow) = "---END OF FOLDER---"
End Sub

 

[6StringJazzer]

Your If statements are checking the wrong worksheet. You are checking the sheet where you are writing your report, rather than the sheet in the workbook you want to check. Also use If/ElseIf instead of independent If because the conditions are mutually exclusive.

Tested.

    For Each sht In FldrWkbk.Sheets
        wb.ActiveSheet.Range("B" & OutputRow) = sht.Name
        If sht.Visible = xlSheetVisible Then
         wb.ActiveSheet.Range("C" & OutputRow) = "Visible"
        ElseIf sht.Visible = xlSheetHidden Then
         wb.ActiveSheet.Range("C" & OutputRow) = "Hidden"
        ElseIf sht.Visible = xlSheetVeryHidden Then
         wb.ActiveSheet.Range("C" & OutputRow) = "Very Hidden"
        End If
        'wb.ActiveSheet.Range("C" & OutputRow) = sht.Visible
        wb.ActiveSheet.Range("A" & OutputRow).ClearContents 'Clear any previous values
        OutputRow = OutputRow + 1
    Next sht

 

[ddander54]

Your If statements are checking the wrong worksheet. You are checking the sheet where you are writing your report, rather than the sheet in the workbook you want to check. Also use If/ElseIf instead of independent If because the conditions are mutually exclusive.

Tested.

    For Each sht In FldrWkbk.Sheets
        wb.ActiveSheet.Range("B" & OutputRow) = sht.Name
        If sht.Visible = xlSheetVisible Then
         wb.ActiveSheet.Range("C" & OutputRow) = "Visible"
        ElseIf sht.Visible = xlSheetHidden Then
         wb.ActiveSheet.Range("C" & OutputRow) = "Hidden"
        ElseIf sht.Visible = xlSheetVeryHidden Then
         wb.ActiveSheet.Range("C" & OutputRow) = "Very Hidden"
        End If
        'wb.ActiveSheet.Range("C" & OutputRow) = sht.Visible
        wb.ActiveSheet.Range("A" & OutputRow).ClearContents 'Clear any previous values
        OutputRow = OutputRow + 1
    Next sht

Doh!!! Guess it helps to look at the correct worksheet! Thanks for 'strumming' me in the right direction!

 

[6StringJazzer]

Glad to help!

I didn't see it myself until I ran your code and reproduced the problem, then saw it right away.