Hide or visible sheets

Question

I am Table Range from B10 to G40, where Column B11 and later indicate Sheet Names (IFRS 1, 2, and 3, for example), and Column C11 through G40 include data validations of Yes or No. I need to create a macro that will show or conceal Sheets IFRS1, 2, and 3 depending on whether a cell in the range C11:G40 has a yes or a no.

If [C11] = "Yes" Then
Sheets("IFRS 1").Visible = True
Else
Sheets("IFRS 1").Visible = False
End If

If [C12] = "Yes" Then
Sheets("IFRS 2").Visible = True
Else
Sheets("IFRS 2").Visible = False
End If

If [C13] = "Yes" Then
Sheets("IFRS 3").Visible = True
Else
Sheets("IFRS 3").Visible = False
End If

If [C14] = "Yes" Then
Sheets("IFRS 5").Visible = True
Else
Sheets("IFRS 5").Visible = False
End If

If [C15] = "Yes" Then
Sheets("IFRS 6").Visible = True
Else
Sheets("IFRS 6").Visible = False
End If

If [C16] = "Yes" Then
Sheets("IFRS 7").Visible = True
Else
Sheets("IFRS 7").Visible = False
End If

If [C17] = "Yes" Then
Sheets("IFRS 13").Visible = True
Else
Sheets("IFRS 13").Visible = False
End If

If [C18] = "Yes" Then
Sheets("IFRS 14").Visible = True
Else
Sheets("IFRS 14").Visible = False
End If

If [C19] = "Yes" Then
Sheets("IFRS 15").Visible = True
Else
Sheets("IFRS 15").Visible = False
End If

If [C20] = "Yes" Then
Sheets("IFRS 16").Visible = True
Else
Sheets("IFRS 16").Visible = False
End If


If [C21] = "Yes" Then
Sheets("IAS 1").Visible = True
Else
Sheets("IAS 1").Visible = False
End If

If [C22] = "Yes" Then
Sheets("IAS 2").Visible = True
Else
Sheets("IAS 2").Visible = False
End If

If [C23] = "Yes" Then
Sheets("IAS 7").Visible = True
Else
Sheets("IAS 7").Visible = False
End If

If [C24] = "Yes" Then
Sheets("IAS 8").Visible = True
Else
Sheets("IAS 8").Visible = False
End If

If [C25] = "Yes" Then
Sheets("IAS 10").Visible = True
Else
Sheets("IAS 10").Visible = False
End If

If [C26] = "Yes" Then
Sheets("IAS 12").Visible = True
Else
Sheets("IAS 12").Visible = False
End If


If [C27] = "Yes" Then
Sheets("IAS 16").Visible = True
Else
Sheets("IAS 16").Visible = False
End If

If [C28] = "Yes" Then
Sheets("IAS 19").Visible = True
Else
Sheets("IAS 19").Visible = False
End If


If [C29] = "Yes" Then
Sheets("IAS 20").Visible = True
Else
Sheets("IAS 20").Visible = False
End If

If [C30] = "Yes" Then
Sheets("IAS 21").Visible = True
Else
Sheets("IAS 21").Visible = False
End If

If [C31] = "Yes" Then
Sheets("IAS 23").Visible = True
Else
Sheets("IAS 23").Visible = False
End If

If [C32] = "Yes" Then
Sheets("IAS 24").Visible = True
Else
Sheets("IAS 24").Visible = False
End If


If [C33] = "Yes" Then
Sheets("IAS 27").Visible = True
Else
Sheets("IAS 27").Visible = False
End If


If [C34] = "Yes" Then
Sheets("IAS 29").Visible = True
Else
Sheets("IAS 29").Visible = False
End If

If [C35] = "Yes" Then
Sheets("IAS 32").Visible = True
Else
Sheets("IAS 32").Visible = False
End If

If [C36] = "Yes" Then
Sheets("IAS 34").Visible = True
Else
Sheets("IAS 34").Visible = False
End If

If [C37] = "Yes" Then
Sheets("IAS 36").Visible = True
Else
Sheets("IAS 36").Visible = False
End If


If [C38] = "Yes" Then
Sheets("IAS 38").Visible = True
Else
Sheets("IAS 38").Visible = False
End If


If [C39] = "Yes" Then
Sheets("IAS 40").Visible = True
Else
Sheets("IAS 40").Visible = False
End If


If [C40] = "Yes" Then
Sheets("IAS 41").Visible = True
Else
Sheets("IAS 41").Visible = False
End If

End Sub

narikkadan · Answer 1 · Feb 10, 2023

Use a loop instead of repeating very similar code:

Sub HideShowSheets()
    Dim wb As Workbook, x As Long, wsSettings As Worksheet
    
    Set wb = ThisWorkbook
    Set wsSettings = wb.Worksheets("settings") 'for example
    
    For x = 1 To 41
        wb.Worksheets("IFRS " & x).Visible = (wsSettings.Range("C10").Offset(x).Value = "Yes")
    Next x
End Sub