excel - Method 'Visible' of object '_Worksheet' failed

Question

Not sure why this is failing but for whatever reason, when the ActiveSheet is Console it fails. Going the other way, it works properly. Code is below.

    Sub Switch_Books()
    Dim ws As Worksheet
    protect_book True

    If ActiveSheet.Name = "Console" Then
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = "CDA Console" Then
                ws.Visible = xlSheetVisible
            Else
                ws.Visible = xlSheetHidden
            End If
        Next ws
    Else
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = "Console" Then
                ws.Visible = xlSheetVisible
            Else
                ws.Visible = xlSheetHidden
            End If
        Next ws
    End If

    protect_book False


    End Sub

question from:https://stackoverflow.com/questions/65599168/method-visible-of-object-worksheet-failed

Answer 1

Try this:

    Sub Switch_Books()
        Dim ws As Worksheet, wsName
        
        protect_book True 'This is a confusing call...
                          ' you should switch the way the boolean works 
    
        wsName = IIf(ActiveSheet.Name = "Console", "CDA Console", "Console")
        ThisWorkbook.Sheets(wsName).Visible = xlSheetVisible 'must be at least one sheet visible
        
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> wsName Then ws.Visible = xlSheetHidden
        Next ws
        
        protect_book False
    
    End Sub