How to unhide worksheets identified by specific cells in main sheet

< Back to Search results

 Category: [Excel]  Demo Available 

How to unhide worksheets identified by specific cells in main sheet




The user has a workbook with about 120 hidden worksheets, where every worksheet contains the accounts for a different company.

Each worksheet is named according to its ID# in the main database (i.e. they are all numeric tab titles)

The user was hoping to add a column to my database with a Dropdown box with two options: SHOW or HIDE.

If set to SHOW, then the corresponding worksheet should be visible. If set to HIDE, then it should be hidden.

So, for example:

1 Company X HIDE
2 Company Y SHOW
3 Company Z HIDE


Is there an IF-THEN-ELSE code that would do this?




A rather simple solution is to have a event macro triggered by your choices ...

Where are the worksheets names actually stored ?

Something along these lines :

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target = "Hide" Then
    Sheets(Target.Offset(0, -1).Value).Visible = False
    Sheets(Target.Offset(0, -1).Value).Visible = True
  End If
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by Carim.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to use VBA - Split Worksheets and Display SaveAs Prompt
How to find a value in a sheet and give back related data to another sheet
How to auto copy data from master list to sub worksheets based on data value in one column
How to change reference columns in another worksheet using VBA looping


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)