Multiple Dependent Checkbox to hide unhide sheet

Hi,
I have a multiple sheets (currently mentioned only few).I have made one master sheet whose name is "Master".In "Master" sheet in C column I have made checkbox whose corresponding worksheet name is in D column.In E column ,I have mentioned another worksheet which is dependent on D column and checkbox mentioned in F column (But I could not be able to link it).

In nutshell,D is main group worksheet name,E is subgroup worksheet name dependent to D and the most important one is that there are other worksheets which is further subgroup of E whose name I have not mentioned here in "Master" sheet.But I have mentioned the E column worksheet name of Master sheet in F1

Now What I want is that when I check on C column and F column checkboxes that worksheets should be visible,rest should be hidden.In addition to it,E column dependent worksheet whose name is mentioned in F1 in any worksheets that should also be visible.

Hope I could able to make it understand.If any information required then kindly let me know.Attachment 17002

Plz help me in this case.

Kindly help me to solve this case.

First thing I did was to give each checkbox a meaningful name to refer to

In ThisWorkbook

Code:

---

Option Explicit

Private Sub Workbook_Open()
    Worksheets("Master").Account.Value = True
    Worksheets("Master").Taxation.Value = True
End Sub

---

and in the code sheet for Master

Code:

---

Option Explicit
Private Sub Account_Click()
   
    Application.EnableEvents = False
    ActiveSheet.Amalagmation.Value = ActiveSheet.Account.Value
    ActiveSheet.CompanyAccount.Value = ActiveSheet.Account.Value
    ActiveSheet.BonusShare.Value = ActiveSheet.Account.Value
    Application.EnableEvents = True
   
    Application.ScreenUpdating = False
    Worksheets("Account").Visible = ActiveSheet.Account.Value
    Worksheets("Amalgamation").Visible = ActiveSheet.Amalagmation.Value
    Worksheets("Company Account").Visible = ActiveSheet.CompanyAccount.Value
    Worksheets("Bonus Share").Visible = ActiveSheet.BonusShare.Value
    Application.ScreenUpdating = True

End Sub

Private Sub Taxation_Click()
    Application.EnableEvents = False
    ActiveSheet.Salary.Value = ActiveSheet.Taxation.Value
    ActiveSheet.PGBP.Value = ActiveSheet.Taxation.Value
    ActiveSheet.IOS.Value = ActiveSheet.Taxation.Value
    ActiveSheet.Depreciation.Value = ActiveSheet.Taxation.Value
    Application.EnableEvents = True

    Application.ScreenUpdating = False
    Worksheets("Taxation").Visible = ActiveSheet.Taxation.Value
    Worksheets("Salary").Visible = ActiveSheet.Salary.Value
    Worksheets("PGBP").Visible = ActiveSheet.PGBP.Value
    Worksheets("IOS").Visible = ActiveSheet.IOS.Value
    Worksheets("Depreciation").Visible = ActiveSheet.Depreciation.Value
    Application.ScreenUpdating = True
End Sub

Private Sub Amalagmation_Click()
    Application.ScreenUpdating = False
    Worksheets("Amalgamation").Visible = ActiveSheet.Amalagmation.Value
    Application.ScreenUpdating = True
End Sub

---

I only did one of the second level buttons, but the rest should be the same as Amalagmation_Click() but with the correct sheet name and correct control name

Thanx for your kind response.I did all the second level buttons as u have said in the post 4.

The most important part is 3rd level.

In 3rd level, I have further three worksheets that do not have any checkboxes and is still visible whose name are -

Sheet12(Com_PM_Q1)
Sheet13(Com_SM_Q2)
Sheet14(Com_SM_Q1)

If you observe carefully in each of these three sheets in F1 you will find the sheet name whose checkbox is mentioned in master sheet here it is E7 (Company Account) and checkbox in F7.

I want further to make these 3 as a dependent sheets to be hidden/visible based on the checkbox checked/unchecked in master sheet of respective name present in F1 in any worksheets. (without making any further checkboxes for these sheets)

In nutshell,the condition to be met are :

The clue is F1 (any worksheet/s), i.e,

if any worksheet/s whose name in F1 = checkbox checked/unchecked in master worksheet respective name should be visible/hidden
Here,

Sheet12(Com_PM_Q1) in F1 =Amalgamation ( in Master sheet).....................................should be visible if it is checked
Sheet13(Com_SM_Q2) in F1 =Company Account ( in Master sheet).....................................should be visible if it is checked
Sheet14(Com_SM_Q1) in F1 =Company Account ( in Master sheet).....................................should be visible if it is checked

NB:

There might be much more worksheets which is to be linked like this as it will be not possible for making for those larger number,the only method is to link the name with master workshet name.

Hope you will help me to find a way out this issue.

Pls find enclosed in attachment after work done of Post 4.

If I understand the question, you just need to add the marked --------------------------- lines

Code:

---

Option Explicit
Private Sub Account_Click()
   
    Application.EnableEvents = False
    ActiveSheet.Amalagmation.Value = ActiveSheet.Account.Value
    ActiveSheet.CompanyAccount.Value = ActiveSheet.Account.Value
    ActiveSheet.BonusShare.Value = ActiveSheet.Account.Value
    Application.EnableEvents = True
   
    Application.ScreenUpdating = False
    Worksheets("Account").Visible = ActiveSheet.Account.Value
    Worksheets("Amalgamation").Visible = ActiveSheet.Amalagmation.Value
    Worksheets("Company Account").Visible = ActiveSheet.CompanyAccount.Value
    Worksheets("Bonus Share").Visible = ActiveSheet.BonusShare.Value
   
    Worksheets("Com_PM_Q1").Visible = Worksheets("Amalgamation").Visible        '----------------
    Worksheets("Com_SM_Q1").Visible = Worksheets("Company Account").Visible    '----------------
    Worksheets("Com_SM_Q2").Visible = Worksheets("Company Account").Visible    '----------------
   
    Application.ScreenUpdating = True

End Sub

Private Sub Amalagmation_Click()
    Application.ScreenUpdating = False
    Worksheets("Amalgamation").Visible = ActiveSheet.Amalagmation.Value
    Worksheets("Com_PM_Q1").Visible = Worksheets("Amalgamation").Visible        '----------------
    Application.ScreenUpdating = True
End Sub

Private Sub CompanyAccount_Click()
    Application.ScreenUpdating = False
    Worksheets("Company Account").Visible = ActiveSheet.CompanyAccount.Value
    Worksheets("Com_SM_Q1").Visible = Worksheets("Company Account").Visible    '----------------
    Worksheets("Com_SM_Q2").Visible = Worksheets("Company Account").Visible    '----------------
    Application.ScreenUpdating = True
End Sub

---

Thanx for your kind reply.

What you did and understood is correct but practically l would be having more than 100 worksheet as stated in Post #5 and it would also not possible to mentioned in code every now and then as it would be too much long for more than 100 worksheets.That's why I have not made checkboxes for those sheets.

Is there is any alternative short method of coding automatically linking by code with reference Sheet name to master which I have stated a sample in Post #5 (level 3 requirement a last requirement and a most important one)

Hope I could be able to make you understand as my English is not that much good that I could express properly.

I would be very much more thankful towards your contribution for giving me time to my post and hope this can be done.

If you put a blank column before every column of sheet names, this code (in the Master sheet code module) will turn the cell to the left into a Marlett check box.

Click on a cell to the left of a sheet name and the sheet's visibility will be changed accordingly.

Code:

---

' in MasterSheet code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Cells.Count = 1 Then
            If SheetExists(.Offset(0, 1).Value) Then
                .Value = IIf(.Value = vbNullString, "a", vbNullString)
                .Font.Name = "Marlett"
                Application.EnableEvents = False
                .Offset(0, 1).Select
                Application.EnableEvents = True
                ShowSheets
            End If
        End If
    End With
End Sub

Function SheetExists(SheetName As String) As Boolean
    On Error Resume Next
    SheetExists = (LCase(ThisWorkbook.Sheets(SheetName).Name) = LCase(SheetName))
    On Error GoTo 0
End Function

Function CheckChainValue(ByVal rIndex As Long, ByVal cIndex As Long) As Boolean
    Dim nextR, nextC
    If rIndex < 1 Or cIndex < 2 Then
        CheckChainValue = True
    ElseIf rIndex = 1 Or cIndex = 2 Then
        CheckChainValue = (Cells(rIndex, cIndex - 1).Value = "a")
    Else
        CheckChainValue = (Cells(rIndex, cIndex - 1).Value = "a")
        nextR = rIndex
        nextC = cIndex - 2
        Do Until (Cells(nextR, nextC) <> vbNullString) Or nextR < 1
            nextR = nextR - 1
        Loop
        CheckChainValue = CheckChainValue And CheckChainValue(nextR, nextC)
    End If
End Function

Sub ShowSheets()
    Dim oneWorksheet As Worksheet
    Dim foundcell As Range
    For Each oneWorksheet In ThisWorkbook.Sheets
        Set foundcell = Me.Cells.Find(oneWorksheet.Name)
        If Not foundcell Is Nothing Then
            oneWorksheet.Visible = IIf(CheckChainValue(foundcell.Row, foundcell.Column), xlSheetVisible, xlSheetHidden)
        End If
    Next oneWorksheet
End Sub

---

One way might be to build an array of worksheet names and use that

The 2 private subs will make a worksheet visible based on another worksheet visibility

There are two conditions in this, but you could add more

Code:

---

Option Explicit

Private Sub pvtCheckThirdLevel()
    Dim v As Variant
   
    For Each v In Array("Com_PM_Q1", "Com_PM_Q2", "Com_PM_Q3", "Com_PM_Q4")
        Call pvtVsibleBasedOnOtherSheet(v, "Amalgamation")
    Next
    For Each v In Array("Com_SM_Q1", "Com_SM_Q2", "Com_SM_Q3", "Com_SM_Q4")
        Call pvtVsibleBasedOnOtherSheet(v, "Company Account")
    Next
End Sub
Private Sub pvtVsibleBasedOnOtherSheet(sChange As Variant, sBasedOn As String)
    On Error Resume Next
    Worksheets(sChange).Visible = Worksheets(sBasedOn).Visible
    On Error GoTo 0
End Sub

Private Sub Account_Click()
   
    Application.EnableEvents = False
    ActiveSheet.Amalagmation.Value = ActiveSheet.Account.Value
    ActiveSheet.CompanyAccount.Value = ActiveSheet.Account.Value
    ActiveSheet.BonusShare.Value = ActiveSheet.Account.Value
    Application.EnableEvents = True
   
    Application.ScreenUpdating = False
    Worksheets("Account").Visible = ActiveSheet.Account.Value
    Worksheets("Amalgamation").Visible = ActiveSheet.Amalagmation.Value
    Worksheets("Company Account").Visible = ActiveSheet.CompanyAccount.Value
    Worksheets("Bonus Share").Visible = ActiveSheet.BonusShare.Value
   
    pvtCheckThirdLevel  '----------------------------------------------
   
    Application.ScreenUpdating = True

End Sub

Private Sub Taxation_Click()
    Application.EnableEvents = False
    ActiveSheet.Salary.Value = ActiveSheet.Taxation.Value
    ActiveSheet.PGBP.Value = ActiveSheet.Taxation.Value
    ActiveSheet.IOS.Value = ActiveSheet.Taxation.Value
    ActiveSheet.Depreciation.Value = ActiveSheet.Taxation.Value
    Application.EnableEvents = True
    Application.ScreenUpdating = False
    Worksheets("Taxation").Visible = ActiveSheet.Taxation.Value
    Worksheets("Salary").Visible = ActiveSheet.Salary.Value
    Worksheets("PGBP").Visible = ActiveSheet.PGBP.Value
    Worksheets("IOS").Visible = ActiveSheet.IOS.Value
    Worksheets("Depreciation").Visible = ActiveSheet.Depreciation.Value
    Application.ScreenUpdating = True
End Sub

Private Sub Amalagmation_Click()
    Application.ScreenUpdating = False
    Worksheets("Amalgamation").Visible = ActiveSheet.Amalagmation.Value
    pvtCheckThirdLevel          '--------------------------------------
    Application.ScreenUpdating = True
End Sub

Private Sub CompanyAccount_Click()
    Application.ScreenUpdating = False
    Worksheets("Company Account").Visible = ActiveSheet.CompanyAccount.Value
    pvtCheckThirdLevel          '----------------------------------------
    Application.ScreenUpdating = True
End Sub

---

Thanx mikerickson.

I think I should have to mentioned the sheet name manually in "Master" sheet.

This will help me avoiding to go in module and put sheet name every now then.

This option is also good.

Great Thanx to Paul_Hossler .

By mentioning sheet name I know it will work which you have done in Post #8.

Thanx again for the for helping me by providing the code and giving time to my query in resolving.