Hiding All Worksheets Unless Cell has a value

R

Rocsalt

New Member
Joined
Aug 6, 2012
Messages
4
Hello.

I have been tasked with finding a way to hide all but one sheets in a workbook until a specific cell has a value - then unhide only the sheets which are associated to the given criteria. I am very new to using VBA, but have so far had the limited ability to modify code i've found on the internet just enough to fit my needs - but that is sketchy and sometimes a lengthy process since I barely understand the code.

In my example I have the following in Excel 2010:

Sheet1 is named "Overview" and is always visible. This sheet has a data validation list in cell E10 with 2 options "Green" and "Baker"
Sheet2 is named "GreenSheet" and is hidden until cell E10 in "Overview" has "Green" as its value
Sheet3 is named "First" and is hidden until cell E10 in "Overview" has "Green" as its value
Sheet4 is named "BakerSheet" and is hidden until cell E10 in "Overview" has "Baker" as its value
Sheet5 is named "Second" and is hidden until cell E10 in "Overview" has "Baker" as its value

If Sheet1 "Overview" cell E10 value is deleted then the associated sheets will be hidden
If Sheet1 "Overview" cell E10 value is changed then the associated sheets will be hidden or made visible based on the value

I searched the net and found a bit of code that will hide all but one sheets on open and I think this is a good start. I have not been able to modify it to also take the cell value into consideration before making this adjustment. I have also not yet gotten to the point of unhiding sheets based on the value of cell E10 in sheet "Overview". I'm hoping you can help me on these two points.

The code I have which hides all sheets on workbook open:
This is being placed into the "ThisWorkbook" object
Code: 
Private Sub Workbook_Open()
 Dim ws As Worksheet
 Application.ScreenUpdating = False
     For Each ws In Worksheets
     If ws.Name = "Overview" Then
     Else
         ws.Visible = xlSheetHidden
     End If
     Next ws
 Application.ScreenUpdating = True
 End Sub

First: I need to code the workbook to check that sheet "Overview" cell E10 has a Value THEN do nothing ELSE hide all other worksheets (This way we keep the current hidden status of existing workbooks)
Second: I need code that will activate when the value of "E10" changes such that
If Sheet "Overview" Cell "E10" = "Baker" Then Unhide sheets "BakerSheet" and "Second" and Hide All other sheets EXCEPT sheet "Overview"
If Sheet "Overveiw" Cell "E10" = "Green" Then Unhide sheets "GreenSheet" and "First" and Hide All other sheets EXCEPT sheet "Overview"

It should be noted that this workbook will contain at least one worksheet that is always hidden.
Also, in my actual workbook there are more than just the two listed sheets which will be hidden or unhidden based on the above criteria and the total number of associated sheets is not the same between the two conditions (for example Green may have only 1 associated sheet while Baker has 5 associated sheets - the total has not yet been fully determined but will be static once determined), and I think/hope I can modify as needed to make it fit.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Modify the Workbook_Open as follows.

Code:
Private Sub Workbook_Open()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name = "Overview" Then
ElseIf Sheets("Overview").Range("E10") = "" Then
ws.Visible = xlSheetHidden
End If
Next ws
Application.ScreenUpdating = True
End Sub
Code:

The following code should be copied to the code module for sheet Overview. Right click the sheet name tab, then click view code in the pop up menu to open th code module.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("E10") Then
If Target.Value = "Baker" Then
Sheets("Baker").Visible = True
Sheets("First").Visible = True
Sheets("Green").Visible = False
Sheets("Second").Visible = False
ElseIf Target.Value = "Green" Then
Sheets("Baker").Visible = False
Sheets("First").Visible = False
Sheets("Green").Visible = True
Sheets("Green").Visible = True
ElseIf Target.Value = "" Then
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Overview" Then
sh.Visible = False
End If
Next
End If
End If
End Sub
Code:
 
Last edited:
Upvote 0
Thank you JLGWhiz

I have adjusted my open code and it works exactly as I need. I can also see that I was on the right track for doing it myself, but i just wasn't getting the code right.

However, I am getting an error when the value in E10 is deleted.
Run-Time Error '13':
Type Mismatch
When going into debug mode it highlights
Code: 
If Target = Range("E10") Then

Any Ideas?
 
Upvote 0
Rocsalt said:
Thank you JLGWhiz

I have adjusted my open code and it works exactly as I need. I can also see that I was on the right track for doing it myself, but i just wasn't getting the code right.

However, I am getting an error when the value in E10 is deleted.
Run-Time Error '13':
Type Mismatch
When going into debug mode it highlights
Code: 
If Target = Range("E10") Then

Any Ideas?
Click to expand...

I started to do this originally and then changed it. Delete the line:

If Target = Range("E10") Then

And replace it with:

If Not Intersect(Target, Range("E10") Is Nothing Then
 
Upvote 0
You will need to use this version because of that change.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intesect(Target, Range("E10") Is Nothing Then
If Target.Value = "Baker" Then
Sheets("Baker").Visible = True
Sheets("First").Visible = True
Sheets("Green").Visible = False
Sheets("Second").Visible = False
ElseIf Target.Value = "Green" Then
Sheets("Baker").Visible = False
Sheets("First").Visible = False
Sheets("Green").Visible = True
Sheets("Green").Visible = True
End If
End If
If Range("E10").Value = "" Then
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Overview" Then
sh.Visible = False
End If
Next
End If
End Sub
Code:
 
Upvote 0
Thanks for the adjustment suggestion. Excel didn't like it as it was, but I noticed we were missing a close paren and added it where it seemed to make sense and also fixed the spelling on Intersect and now it seems to work. The adjustments I made are as follows:


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Inte[COLOR=#ff0000]r[/COLOR]sect(Target, Range("E10")[COLOR=#ff0000])[/COLOR] Is Nothing Then
If Target.Value = "Baker" Then
Sheets("Baker").Visible = True
Sheets("First").Visible = True
Sheets("Green").Visible = False
Sheets("Second").Visible = False
ElseIf Target.Value = "Green" Then
Sheets("Baker").Visible = False
Sheets("First").Visible = False
Sheets("Green").Visible = True
Sheets("Green").Visible = True
End If
End If
If Range("E10").Value = "" Then
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Overview" Then
sh.Visible = False
End If
Next
End If
End Sub

Thank you a lot for your help, it has saved me a ton of time with my own trial and error methods.

Though we're still getting the run-time error Type Mismatch on the following code when the value in cell E10 is deleted:
Code: 
If Target.Value = "Baker" Then

With this, i'd assume it will also give us the same error on the next statement that is exactly the same
Code: 
ElseIf Target.Value = "Green" Then
 
Last edited:
Upvote 0
Rocsalt said:
Thanks for the adjustment suggestion. Excel didn't like it as it was, but I noticed we were missing a close paren and added it where it seemed to make sense and also fixed the spelling on Intersect and now it seems to work. The adjustments I made are as follows:


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Inte[COLOR=#ff0000]r[/COLOR]sect(Target, Range("E10")[COLOR=#ff0000])[/COLOR] Is Nothing Then
If Target.Value = "Baker" Then
Sheets("Baker").Visible = True
Sheets("First").Visible = True
Sheets("Green").Visible = False
Sheets("Second").Visible = False
ElseIf Target.Value = "Green" Then
Sheets("Baker").Visible = False
Sheets("First").Visible = False
Sheets("Green").Visible = True
Sheets("Green").Visible = True
End If
End If
If Range("E10").Value = "" Then
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Overview" Then
sh.Visible = False
End If
Next
End If
End Sub

Thank you a lot for your help, it has saved me a ton of time with my own trial and error methods.

Though we're still getting the run-time error Type Mismatch on the following code when the value in cell E10 is deleted:
Code: 
If Target.Value = "Baker" Then

With this, i'd assume it will also give us the same error on the next statement that is exactly the same
Code: 
ElseIf Target.Value = "Green" Then
Click to expand...

I blame the typos on arthritis. Glad you got it working.

Regards, JLG
 
Upvote 0
I cannot get it to duplicate the error with the type mismatch. BTW if you have not already caught it, one of these needs to be changed to "Second"

Code:
Sheets("Green").Visible = True
Sheets("Green").Visible = True
Code:
 
Upvote 0
Thank you, I did catch that while modifying for the extra sheets. I'll re-revisit it. The error is popping up only when I delete the value in E10, otherwise it is doing very well.

I am still getting the error. I tried modifying the code, however, and am not getting the error when I use the following:

Code: 
If Not Intersect(Target, Range("E10")) <> "Baker" Then

And as I figured the error is popping up on the next line, so I will modify it the same way and see what we get...
I modified the next code as follows:
Code: 
ElseIf Not Intersect(Target, Range("E10")) <> "Green" Then

Now there are no errors. I don't know if it's just a variance in our versions or what, but it works now. Your original modification gave me the idea to make this modification, so thank you for that - and i'm surprised I got it right with so little effort -

I appreciate your time! This is the final code, for anyone with a similar issue:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("E10")) Is Nothing Then
    If Not Intersect(Target, Range("E10")) <> "Baker" Then
        Sheets("Baker").Visible = False
        Sheets("First").Visible = False
        Sheets("Third & Fourth").Visible = False
        Sheets("Fifth").Visible = False
        Sheets("Green").Visible = True
        Sheets("Sixth").Visible = False
    ElseIf Not Intersect(Target, Range("E10")) <> "Green" Then
        Sheets("Baker").Visible = True
        Sheets("First").Visible = True
        Sheets("Third & Fourth").Visible = True
        Sheets("Fifth").Visible = True
        Sheets("Green").Visible = False
        Sheets("Sixth").Visible = True
    End If
 End If
 If Range("E10").Value = "" Then
    For Each sh In ThisWorkbook.Sheets
    If sh.Name <> "Overview" Then
        sh.Visible = False
    End If
 Next
 End If
 End Sub

I may play with it a little more as things change, but this is perfect for now.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Hiding rows based on cell value
Replies
2
Views
222
smozgur
smozgur
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
356
mumps
mumps
sparky2205
  • Solved
Toggle CheckBox to Hide/Unhide worksheet
Replies
6
Views
447
sparky2205
sparky2205
E
  • Question
VBA to open hyperlink in a hidden sheet which also containing hyperlink
Replies
2
Views
936
ErynK
E
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
548
kevin9999
K

Forum statistics

Threads
1,215,750
Messages
6,126,663
Members
449,326
Latest member
asp123

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top