Multiple Dependent Checkbox to hide unhide sheet

[paradise]

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.17002

[paradise]

Plz help me in this case.

[paradise]

Kindly help me to solve this case.

[Paul_Hossler]

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

In ThisWorkbook



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



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

[paradise]

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.

[Paul_Hossler]

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




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

[paradise]

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.

[mikerickson]

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.


' 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

[Paul_Hossler]

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





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

[paradise]

Thanx mikerickson (http://www.vbaexpress.com/forum/member.php?10706-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.

[paradise]

Great Thanx to Paul_Hossler (http://www.vbaexpress.com/forum/member.php?9803-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.