Search code examples
arraysexcelvbashow-hideworksheet

Excel VBA, UnHiding Sheets in array, Run-time error '13': Type mismatch

I'm trying to unhide specific sheets using an array, but get an Run-time error '13'. It's this specific line of code that gives me the error:

Worksheets(WS).Visible = True

I managed to hide the specific sheets using an array (I refer to the CodeNames of the sheets to avoid future refering problems), see code below:

Sub Hide_sheets_By_CodeName()

Dim Ws_Array As Variant
Set Ws_Array = Sheets(Array(Tiger.Name, Dog.Name, Cat.Name))
Ws_Array.Visible = False

End Sub

To Unhide them I've read that you have to loop through each sheet in the array. I have used this code for that (and here I get the error):

Sub UnHide_sheets_By_CodeName()
Dim WS As Worksheet
Dim Ws_Array As Variant

Set Ws_Array = Sheets(Array(Tiger.Name, Dog.Name, Cat.Name))

For Each WS In Ws_Array
    Worksheets(WS).Visible = True

Next

End Sub

Any help is highly appreciated :)

Solution

  • WS_Array is an object of Type Sheets, i.e. a member is a sheet. Therefore Worksheets(WS).Visible = True runs into error 13 as Worksheets(name of sheet) expects a string and not an object.

    Either you change your for loop to 

    For Each WS In Ws_Array    
    Worksheets(WS.Name).Visible = xlSheetVisible
Next

    or to

    For Each WS In Ws_Array
    WS.Visible = xlSheetVisible    
Next