Hide or visible sheets

0 votes

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
Feb 10, 2023 in Others by Kithuzzz
• 38,010 points
337 views

1 answer to this question.

0 votes

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
answered Feb 10, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How do I get text in a pivot table in excel or google sheets?

Try: =QUERY(QUERY({Data!A:A, Data!Q:S}, "select Col4,max(Col1) ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
864 views
0 votes
1 answer

Divide data and copy other cells in google sheets or excel

Try this : const Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`YourSheetName`) function dataHandler() ...READ MORE

answered Mar 20, 2023 in Others by narikkadan
• 63,420 points
225 views
0 votes
1 answer

How to check if array is multidimensional or not?

Since the 'second dimension' could be just ...READ MORE

answered Nov 5, 2018 in Others by DataKing99
• 8,240 points
5,207 views
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
877 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,183 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
481 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
731 views
0 votes
1 answer

Excel: How to merge two columns into one (from different sheets or separated columns)

This equation is completely adjustable. Your two ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
529 views
0 votes
1 answer

What is the better API to Reading Excel sheets in java - JXL or Apache POI

Here are the things where both APIs ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,420 points
2,606 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP