Question - Hide tabs based on different cells

[jlabrecque]

Hi,


So I am trying to hide and show tabs based on different values


I have a cover page, and on that cover page, I have four items listed we will call them (A, B, C, D). If "Yes" is next to A I want it to show tab A. If no is next to tab A, I want it to hide tab A. Then so on for B, C, D. I am having a bit of trouble separating the four different questions. Can someone please assist me with this. Below is what I have so far.


Note: I want the A, B, C, D questions to be completely independent of one another

Private Sub Worksheet_Change(ByVal Target As Range)
If [Tax_acct] = "Yes" Then
Sheets("Tax Account").Visible = True
Else
Sheets("Tax Account").Visible = False
End If
End Sub

 

[My Aswer Is This]

I need more specific details.
Your wanting a sheet change event script

So when you change a value in what cell you want what to happen?

Are you saying If Range("A1") is change to George you want the sheet named George made visible
But if A1 is changed to any other value Sheet named George is hidden.

I do not understand:

I have four items listed we will call them (A, B, C, D)

What is a item?​

 

[jlabrecque]

Thank you for your response.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Select an answer[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Each item in this case A,B,C,D would have a tab associated with it. In this case I would want tabs A and D to not be hidden and B and C to be hidden.

Essentially answering specific questions on a cover page to un hide other tabs.

I hope that makes sense

 

[My Aswer Is This]

This script assumes you have sheet names in Column A

And if you enter Yes in column B the sheet name in column A same row will be visible
If any other value is entered the sheet will be hidden

So If B1 Has Yes entered
And A1 has "Mary" already entered the sheet named Mary will be visible

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/7/2018  4:13:37 PM  EST
On Error GoTo M
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Value = "Yes" Then
        Sheets(Target.Offset(, -1).Value).Visible = True
        Else
        Sheets(Target.Offset(, -1).Value).Visible = False
    End If
End If
Exit Sub
M:
MsgBox "You have no sheet named   " & Target.Offset(, -1).Value
End Sub

 

[jlabrecque]

Thank you so much

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.