CODEWITHSUNDEEP
vbaexcelcollections
CamReed
CamReed

Reputation: 13

Is it possible to compare an object property against objects in a collection?

Here is the code I am trying to work with. It is supposed to expand and collapse a number of sheets when the show/hide sheet is clicked. I have a working iteration of this, but the workbook will be over 200 sheets when complete and I would like to have these specified as collections that I can reference instead of pointing to each sheet that should be expanded or collapsed specifically. The second for loop is how I have the code set up in my other iteration and it works, wondering if there is a way to make the first for loop work. Cheers!

Private Sub Worksheet_Activate()
Dim sheet As Worksheet
Dim col1 As New Collection
Dim col2 As New Collection
col1.Add (BASE1) And (Cap1) And (A1) And (A2) And (CAP0) And (BASE0) And (Sheet13)
col2.Add (BASE0) And (CAP0) And (BASE1) And (Cap1) And (BASE2) And (Cap2) And (Sheet13)
Application.ScreenUpdating = False
If Sheet13.Name = "Show" Then
    For Each sheet In ThisWorkbook.Sheets
       If (sheet.Name <> col2) Then
        sheet.Visible = xlSheetVisible
       End If
    Next sheet
    Sheet13.Name = "Collapse"
    Sheet3.Activate
Else
    For Each sheet In ThisWorkbook.Sheets
        If (sheet.Name <> Sheet1.Name And sheet.Name <> Sheet2.Name And sheet.Name <> Sheet3.Name And sheet.Name <> Sheet13.Name And sheet.Name <> Sheet7.Name) Then
           sheet.Visible = xlSheetVeryHidden
        End If
    Next sheet
    Sheet13.Name = "Show"
    Sheet3.Activate
End If
Application.ScreenUpdating = True
End Sub

Hoping this will help clear up my question. The below code is the current working version of the above code without the use of collections. AS you can see, the sheets to expand and collapse are referenced specifically. What I want to do is If sheet.Name <> (Any sheet in collection1) Then

Private Sub Worksheet_Activate()
Dim sheet As Worksheet
Application.ScreenUpdating = False
If Sheet8.Name = "702 BASE - EXPAND" Then
    For Each sheet In ThisWorkbook.Sheets
        If (sheet.Name <> Sheet4.Name And sheet.Name <> Sheet5.Name And sheet.Name <> Sheet6.Name And sheet.Name <> Sheet14.Name And sheet.Name <> Sheet15.Name And sheet.Name <> Sheet16.Name And sheet.Name <> Sheet17.Name And sheet.Name <> Sheet18.Name And sheet.Name <> Sheet19.Name And sheet.Name <> Sheet20.Name And sheet.Name <> Sheet21.Name And sheet.Name <> Sheet22.Name And sheet.Name <> Sheet23.Name And sheet.Name <> Sheet24.Name And sheet.Name <> Sheet25.Name And sheet.Name <> Sheet26.Name) Then
            sheet.Visible = xlSheetVisible
        End If
    Next sheet
    Sheet8.Name = "702 BASE - COLLAPSE"
    Sheet8.Activate
Else
    For Each sheet In ThisWorkbook.Sheets
        If (sheet.Name <> Sheet4.Name And sheet.Name <> Sheet8.Name And sheet.Name <> Sheet1.Name And sheet.Name <> Sheet2.Name And sheet.Name <> Sheet3.Name And sheet.Name <> Sheet7.Name) Then
           sheet.Visible = xlSheetVeryHidden
        End If
    Next sheet
    Sheet8.Name = "702 BASE - EXPAND"
    Sheet1.Activate
End If
Application.ScreenUpdating = True
End Sub

Upvotes: 1

Views: 75

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Here's a simplified version of your code to illustrate one possible approach:

Private Sub Worksheet_Activate()

    Dim sheet As Worksheet
    Dim arr1, arr2

    arr1 = Array(BASE1, Cap1, A1, A2, CAP0, BASE0, Sheet13)

    Application.ScreenUpdating = False

    If Sheet13.Name = "Show" Then '<< can use "Me.Name" if code is in Sheet13 module...
        For Each sheet In ThisWorkbook.Sheets
           If Not InArray(sheet, arr1) Then sheet.Visible = xlSheetVisible
        Next sheet
        Sheet13.Name = "Collapse"
        Sheet3.Activate
    End If

    Application.ScreenUpdating = True

End Sub

A utility function to check if a sheet is in the array provided. Should go in a regular code module.

'is a sheet in the provided array?
Function InArray(sht As Worksheet, arr)
    Dim s, rv As Boolean
    For Each s In arr
        If s.Name = sht.Name Then
            rv = True
            Exit For
        End If
    Next s
    InArray = rv
End Function

Upvotes: 1

Related Questions