CODEWITHSUNDEEP
arraysexcelvbaworksheet
gbra
gbra

Reputation: 53

Run specific macro if ActiveSheet.Name is listed in an array

I have written a macro to call one of 3 subs, depending on the ActiveSheet.Name. It works in the first two cases, but not the third - if the active sheet name is one of those listed in an array.

Please can someone advise me how I can correct my code?

Sub TBtnYR_Click()

' Hides Year R Columns

Dim mySheets As Sheets

Set mySheets = Sheets(Array(Sheet21.Name, Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet9.Name, _
    Sheet10.Name, Sheet11.Name, Sheet16.Name, Sheet17.Name, Sheet18.Name))

    If ActiveSheet.Name = "Bookbands" Or ActiveSheet.Name = "KS1 - TRP" Then
        BookbandsandTRPYR
    ElseIf ActiveSheet.Name = "RWM" Then
        RWMYR
    ElseIf ActiveSheet.Name = mySheets.Item(ActiveSheet.Index).Name Then
        OtherSubjsYR
    End If

End Sub

Many thanks.

Upvotes: 0

Views: 1673

Answers (2)

gbra
gbra

Reputation: 53

Following the advice from @Gary's Student, I had to search for the solution as my VBA knowledge isn't secure enough yet to generate the right syntax.

I found and amended similar code to create this solution:

Sub TBtnYR_Click()

' Hides Year R Columns

Dim ArrayElement As Variant
Dim OtherSubjects(0 To 9) As String

OtherSubjects(0) = "Art"
OtherSubjects(1) = "Computing"
OtherSubjects(2) = "Design Technology"
OtherSubjects(3) = "Geography"
OtherSubjects(4) = "History_"
OtherSubjects(5) = "MFL"
OtherSubjects(6) = "Music"
OtherSubjects(7) = "PE"
OtherSubjects(8) = "RE"
OtherSubjects(9) = "Science"

    If ActiveSheet.Name = "Bookbands" Or ActiveSheet.Name = "KS1 - TRP" Then
        BookbandsandTRPYR
    ElseIf ActiveSheet.Name = "RWM" Then
        RWMYR
    Else
    For Each ArrayElement In OtherSubjects
        If ActiveSheet.Name = ArrayElement Then
        OtherSubjsYR
    End If
    Next
    End If

End Sub

I've not seen an array created in this way before but I understand how it works. For my future knowledge, would anyone else do it in a different or more efficient way?

Upvotes: 0

QHarr
QHarr

Reputation: 84465

As mentioned in comments you can use an array of sheet names. In the third condition test I would then use Application.Match to see if Activesheet.Name is in the array:

Dim arrSheets()
arrSheets = Array("Sheet1", "Sheet2")

'your code   
ElseIf Not IsError(Application.Match(ActiveSheet.Name, arrSheets, 0)) Then
    Debug.Print "Tada"
End If

Upvotes: 1

Related Questions