How to unhide worksheets identified by specific cells in main sheet
Category: [Excel] Demo Available
How to unhide worksheets identified by specific cells in main sheet
Requirement:
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
Etc...
Is there an IF-THEN-ELSE code that would do this?
Solution:
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 :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "Hide" Then
Sheets(Target.Offset(0, -1).Value).Visible = False
Else
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.