Hide/Unhide Sheets with VBA

[GDunn]

Hi,

Have created a combo-box with a list of values. Depending on value in linked cell I then want to hide and unhide certain sheets.

I have tried to create an If command with Array for lists of sheets with ElseIf.
Works fine for hiding sheets, but get 'Run-time error '1004': Unable to set the Visible property of the Sheets class' when unhiding.

Please help point out where I am going wrong with this code:

Sub test()
'
' test Macro
'
If Range("A3") = 1 Then
Worksheets(Array("Test", "QLDRevenue", "QLDSummary", "QLDComments", "NSWRevenue", "NSWSummary", _
"NSWComments")).Visible = True
Worksheets("Test").Select

ElseIf Range("A3") = 2 Then
Worksheets(Array("Test", "QLDRevenue", "QLDSummary", "QLDComments")).Visible = True

Worksheets(Array("NSWRevenue", "NSWSummary", "NSWComments")).Visible = False
Worksheets("Test").Select
End If


End Sub


Thanks in advance

 

[GlennUK]

It's probably simply to do with the way Excel operates ... doing things manually you can group sheets and hide in one operation ... but with hidden sheets you can only do the unhide one sheet at a time. Using that logic in your macro, it works :

Sub test()
'
' test Macro
'
If Range("A3") = 1 Then
    mylist = Array("Test", "QLDRevenue", "QLDSummary", "QLDComments", "NSWRevenue", "NSWSummary", _
    "NSWComments")
    For Each el In mylist
        Worksheets(el).Visible = True
    Next
    Worksheets("Test").Select

ElseIf Range("A3") = 2 Then
    mylist = Array("Test", "QLDRevenue", "QLDSummary", "QLDComments")
    For Each el In mylist
        Worksheets(el).Visible = True
    Next

    Worksheets(Array("NSWRevenue", "NSWSummary", "NSWComments")).Visible = False
    Worksheets("Test").Select
End If

 

[dannel]

While i try the to use the below code to hide sheets based on a condition.. iget an error

Run Time Error 424 :
Object required..

What am i doing wrong ???

Thanks

Sub test()
'
' test Macro
Dim el As Worksheet
Dim Mylist As Variant

If Range("A3") = 1 Then
    Mylist = Array("Test", "Shas", "Infi", "Elcome")
    For Each el In Mylist
        Worksheets(el).Visible = True
    Next
    Worksheets("Test").Select

ElseIf Range("A3") = 2 Then
    Mylist = Array("Test", "Prestige", "ILPL", "KRM")
    For Each el In Mylist
        Worksheets(el).Visible = True
    Next

    Worksheets(Array("Shas", "Infi", "Elcome")).Visible = False
    Worksheets("Test").Select
End If
End Sub

 

[GlennUK]

How about actually telling us all on what line the error is occurring?