Hiding worksheets, conditional on the content of a cell

P

prclark

New Member
Joined
Mar 17, 2014
Messages
11
Hi, I think people have asked broadly similar in the past but most questions seem to be about changing the content of a single cell.

I have 10 worksheets of which I would like 9 normally hidden.

On the first sheet I have a row of 10 cells, C7 to L7 (lets call them 1-10). All but the first cell, C7, will be normally empty.

If a number is placed in to any of the other cells, D7 to L7 (2-10), I would like a corresponding worksheet to be unhidden. If the cell is empty then I would like the worksheet to be hidden again

Can you help me with the VB code to get this to take place?

Thanks!

P
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Welcome to the Board!

Go to your first sheet, click on the sheet tab name at the bottom of the screen, select View Code and paste this code in the resulting VB Editor:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


'   Check to see if a cell in range D7:L7 is being updated
    If Not Intersect(Target, Range("D7:L7")) Is Nothing Then
'       If cell is updated with a number greater than zero make that sheet visible
        For Each cell In Target
            If cell > 0 Then
                Sheets(cell.Column - 2).Visible = True
'       ... else is cell is updated to empty, hide the sheet
            Else
                If Len(cell) = 0 Then Sheets(cell.Column - 2).Visible = False
            End If
        Next cell
    End If


End Sub
I think it should do what you want. It runs as values in D7:L7 are updated.
 
Upvote 0
Thanks for the swift response (and kind welcome)

I should have said that I have more than these 10 tabs in the workbook so this won't work in my particular case. Is there a way of saying if D7 > 0 then show Sheet7, and so on? I realise it probably is very inelegant but that sort of sums me up!

Really appreciate the help!

P
 
Upvote 0
I should have said that I have more than these 10 tabs in the workbook so this won't work in my particular case. Is there a way of saying if D7 > 0 then show Sheet7, and so on? I realise it probably is very inelegant but that sort of sums me up!
Click to expand...
It doesn't really matter how many sheets there are, as long as it is sequential it will work, i.e.

D7 corresponds to Sheet2
E7 corresponds to Sheet3
F7 corresponds to Sheet4
...

Even if it is not exactly the condition outline above, if it follows any sort of "pattern", we can adjust the code to work.
You just need to let us know that the pattern is.
 
Upvote 0
Hi prclark,

Try this:
Code: 
Sub Hide_Unhide_Sheets()
    Dim i As Range
    Dim c As Integer
    c = 1
    For Each i In Range("D7:L7")
        c = c + 1
        On Error Resume Next
        If i = "" Then Sheets(Array(c)).Visible = False
        If i <> "" Then Sheets(Array(c)).Visible = True
     Next
End Sub
 
Upvote 0
E7 corresponds to sheet 18, F7 to sheet 19 etc. What is the correct way to code that?

Thanks
 
Upvote 0
The key is this part here:
Code: 
Sheets(cell.Column - 2)
Basically, the number is the difference between the column number and the corresponding sheet number.
So, if E7 corresponds to Sheet 18, E is column 5, so 18 - 5 -13.
So, that formula should be updated to:
Code: 
Sheets(cell.Column - 13)

Make sense?
 
Upvote 0
Yes, it makes sense but it is returning 'out of range'. I tried + 13 but didn't work. Thoughts?
 
Upvote 0
Whoops, you are right. We are going the other direction, so it should be +13, not -13.
Be sure to update it in both places too.
Also, be sure to update the Range("D7:L7") reference to reflect your changes.

How is it not working?
What are you entering in to the cell?

If it is still not working, add these message boxes:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


'   Check to see if a cell in range D7:L7 is being updated
    If Not Intersect(Target, Range("D7:L7")) Is Nothing Then
'       If cell is updated with a number greater than zero make that sheet visible
        For Each cell In Target
            If cell > 0 Then
                MsgBox "About to unhide sheet " & cell.Column + 13
                Sheets(cell.Column + 13).Visible = True
'       ... else is cell is updated to empty, hide the sheet
            Else
                If Len(cell) = 0 Then
                    MsgBox "About to hide sheet " & cell.Column + 13
                    Sheets(cell.Column + 13).Visible = False
                End If
            End If
        Next cell
    End If


End Sub
and re-run. It should tell you what it is going to hide/unhide before it does it.
Do you get those pop-ups when you make changes to that range?
 
Last edited:
Upvote 0
Odd, it is sort of working but E7 is controlling Sheet11, F7 is controlling Sheet10, G7 does Sheet12, H7 does 13, rest seem to do nothing

Message box is a nice touch and works fine!

Any ideas?

P
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Hiding rows based on cell value
Replies
2
Views
97
smozgur
smozgur
D
  • Question
VBA to hide rows when a cell is a specific value and then hide the associated worksheet
Replies
3
Views
364
NoSparks
N
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
931
dmt32
D
D
  • Question
VBA code to Hide/Unhide a Worksheet in a Workbook
Replies
1
Views
414
rlv01
R
U
  • Question
Type Mismatch Error
Replies
9
Views
190
Alex Blakenburg
A

Forum statistics

Threads
1,214,885
Messages
6,122,090
Members
449,065
Latest member
Danger_SF

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