Call sheet based on YES/NO

[Adele05]

I have a workbook with many sheets. I have an index worksheet that list the names of the worksheets in column B and a Yes/No question in column C. I am trying a VBA a code that when I type "Yes" it will open the worksheet that it corresponds to. When I type "No" it will hide the worksheet. The names in the list exactly match the name of the worksheets.

Range is from B1to C36

 

[Anthony47]

Use WorksheetChange Event:
1) Rightclick on the tab with the sheetname Index; selech Display code. This will open the macro editor at "the right position"
2) Copy the following code and paste it into the empty frame of the macro editor

Then return to Index and try
This should show the sheet when Yes is typed, and hide it in all the other conditions

 

[Anthony47]

You was certainly asking "where is my code?"
Ehmm... It was left in my test workbook, and I just noticed it now by accident
Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myC As Range, ckArea As String
'
ckArea = "C2:C100"         '<<< The range with  the sheet names
'
If Not Application.Intersect(Target, Range(ckArea)) Is Nothing Then
    For Each myC In Application.Intersect(Target, Range(ckArea))
        If myC.Value = "Yes" Then
            Sheets(myC.Offset(0, -1).Value).Visible = True
        Else
            Sheets(myC.Offset(0, -1).Value).Visible = False
        End If
    Next myC
End If
End Sub

 

[Adele05]

Thank you very much - I appreciate it!

 

[Breeski19]

@Anthony47 Would this work with checkboxes instead of yes and no? Meaning when the checkbox is checked that sheet/tab would show and if left unchecked it would be hidden?

If so how would that change the code? What would the VBA input look like?

 

[Anthony47]

It can be done, but...
A checkbox doesn't trigger a WorksheetChange event, so you need to use for example the CheckBox_Click event itself, for each of the checkboxes.
This means:
-you create the CheckBoxes, using the ActiveX control
-open the worksheet vba module (rightclick the tab with the name of the worksheet, select Display code)
-with reference to the attached image, click the highlighted drop down; select each of the checkboxes, and a Private Sub CheckBoxX_Click will be initialized
-add in this Sub the instruction to show/hide the associated worksheet; for example

Private Sub CheckBox1_Click()
    Sheets("Foglio2").Visible = CheckBox1.Value
End Sub

Private Sub CheckBox2_Click()
    Sheets("Foglio5").Visible = CheckBox1.Value
End Sub

'etc etc

Other tricks could be developed, but I think the advantages would be marginal