Use Button to Hide/Unhide Tabs Based on Cell Value

[GBM15228]

Hello:

I am almost completely new to VBA and trying to add code to an excel workbook to automate the display of tabs based on a cell condition.

On a main tab, let's call it 'CONTROL', I list a series of 9 tabs which are hidden in the workbook. These are named alphabetically from 'A' thru 'I'. Next to this list of tab names I have a conditional cell which reads "Yes" or "No". To illustrate:

Col. A Col. B
Tab Name: Review?:
A Yes
B No
C Yes
D Yes
E No
F No
G No
H No
I No

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
I would like to include two buttons on this worksheet.

When you click the first button, it will unhide each tab from column A noted "Yes" in column B. So, using the above example, clicking the button would unhide only tabs 'A', 'C', and 'D'. Of course, the tabs to be unhidden could change since whether a given tab is noted "Yes" or "No" is dependent on a separate formula I have written to each cell. So, if you were to click the button once it would display tabs 'A', 'C' and 'D'. If the conditions then change to only require review of tabs 'A' and 'C', clicking the button again should display only tabs 'A' and C'.

The second button would hide all tabs 'A' thru 'I' indiscriminately.

After googling and searching this forum, I cannot determine the appropriate code which will accomplish these tasks.
Any help is appreciated.

Thanks,
Greg

 

[Runsk1]

Hello!

EDIT: These macros should work if your sheet-names are in cells A1:A9 and Yes/No markers in B1:B9.
EDIT:EDIT: Oh, I used "Sheet1" instead of "CONTROL".. you'll figure it out.

Try these two macros:

Show sheets that are marked with "Yes":

Sub showtabs()

Dim rng As Range
Set rng = ThisWorkbook.Sheets("Sheet1").Cells.Range("A1:A9")
For Each c In rng
If c.Offset(0, 1).Value = "Yes" Then
Sheets(c.Value).Visible = True
End If
Next
End Sub

And Hide all sheets:

Sub hidetabs()
Dim rng As Range
Set rng = ThisWorkbook.Sheets("Sheet1").Cells.Range("a1:A9")
For Each c In rng
ThisWorkbook.Sheets(c.Value).Visible = False
Next
End Sub

You should change the ranges to match your workbook, then just assign the macros to buttons.

Hope it works as you wanted.



Matias

 

[Runsk1]

Slight change for first macro, I forgot it was supposed to hide unwanted sheets if something changes.

Sub showtabs()
Dim rng As Range
Set rng = ThisWorkbook.Sheets("Taul1").Cells.Range("A1:A4")
For Each c In rng
If c.Offset(0, 1).Value = "Yes" Then
Sheets(c.Value).Visible = True
Else:
Sheets(c.Value).Visible = False
End If
Next
End Sub

 

[GBM15228]

Hi Matias (Runsk1) - That worked like a charm. Thank you for the code and for the instructions! Have a great day.