Excel VBA hide sheets with names in array

Question

I want to hide all tabs whose names are "ABC", "DEF", "GHI"

This is my code:

Sub hideSheets()
Dim ws As Worksheet
invisible = Array("ABC", "DEF", "GHI")

For Each ws In ActiveWorkbook.Worksheets
    For Each nome In invisible
        If ws.Name = nome Then
            ws.Visible = False
        Else
            ws.Visible = True
        End If
    Next nome

Next ws

End Sub

Which isn't hiding any sheet.

This has been answered [here](http://stackoverflow.com/questions/853270/hiding-an-excel-sheet). You need to use: xlSheetVeryHidden, xlSheetHidden or xlSheetVisible after the `=` instead of true or false. — Scott Craner, Oct 14 '15 at 16:31
@ScottCraner That question's answers do not address the array component in the title of the question. In addition your comment, while not wrong, is misleading. `xlSheetHidden = 0`, which is exactly equivalent to False , `xlSheetVisible = -1` which is exactly equivalent to True. So those two visible states can and often are achieved by using the simple Boolean values. `xlSheetVeryHidden = 2` which can be set either with the constant or the literal or an expression. In any case, nothing from that other question addresses this question. — Excel Hero, Oct 14 '15 at 17:00
@ScottCraner :) Well no need for that, but the more knowledge we share the better everyone becomes! — Excel Hero, Oct 14 '15 at 17:05

Excel Hero · Accepted Answer · 2015-10-14T17:06:34.280

3

To hide the sheets in your invisible array, it is as simple as this:

Sheets(invisible).Visible = False

Note: this works well for hiding. To make the sheets visible again, you must set each sheet visible one by one; you cannot use the array in one line like you can for hiding.

edited Oct 14 '15 at 17:06

answered Oct 14 '15 at 16:47

Excel Hero

14,253
4
33
40

LOL! this is really simple! – Oct 14 '15 at 16:48
Any other simple way of unhiding anything that isn't in that array? – Oct 14 '15 at 16:50
Nope. Unhiding requires doing one sheet at a time. – Excel Hero Oct 14 '15 at 16:51
Ok, I've unhidden all the sheets one by one and then hidden the ones in the array. Thanks a lot! – Oct 14 '15 at 16:52
Thanks @NunoNogueira. – Excel Hero Oct 16 '15 at 11:39

score 1 · Answer 2 · answered Oct 14 '15 at 16:38

Take out the else. It hides it but on the next loop, it unhides again.

Dim ws As Worksheet
invisible = Array("ABC", "DEF", "GHI")

For Each ws In ActiveWorkbook.WorkSheets

    For Each nome In invisible
        If ws.name = nome Then
            ws.Visible = False
        End If
    Next nome

Next ws

Excel VBA hide sheets with names in array

2 Answers2

Linked