INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Hide Worksheet Based on Cell Value

Hide Worksheet Based on Cell Value

(OP)
Hi All,

I have searched far and wide for answers to this, and there are some that are close, but none that suits my specific needs.
I have a workbook with over 100 sheets, each named, and not in order (ie sheet 10 could be the 30th sheet). On the first sheet called "Trade List", I have all teh sheets named in Column D (from D10 to D110). In column E (from E10 to E110) I have a "1" or "0" depending if I want teh corresponding worksheet visable (1 = viable, 0 = hidden).

What VBA code do I need to put into "Trade List" worksheet, so the respective sheets will hide/unhide based on values in column E?

I have attached a shrunk down version of the workbook which may help to understand what I have. Basically, if tehre is a 1 (shown as a tick) in column E, the shhet needs to be unhidden. Jote Summary, Clinet Suummary will always be visable.

Any help would be greatly appreciated.
Thanks

RE: Hide Worksheet Based on Cell Value

Hi,

You will need VBA (macro)

CODE

Sub HideUnhideSheets()
Dim r as Range

For Each r in Sheets("Trade List").Range("D12:D110")
   Select Case r.offset(0,1).Value
      Case 1
          Sheets(r.Value).Hidden = xlSheetVisible
      Case 0
          Sheets(r.Value).Hidden = xlSheetHidden
   End Select
Next
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hide Worksheet Based on Cell Value

(OP)
Hi Skip,
I tried to copy the code into a few places (Module 1, into code for Trade List worksheet) and couldn't get this to work.

I did note I got teh column wrong, should have been C and not D so I changed this alomg with the offset - still didn't work as below.

Sub HideUnhideSheets()
Dim r As Range

For Each r In Sheets("Trade List").Range("C12:C110")
Select Case r.Offset(0, 2).Value
Case 1
Sheets(r.Value).Hidden = xlSheetVisible
Case 0
Sheets(r.Value).Hidden = xlSheetHidden
End Select
Next
End Sub


Any chance you could paste into correct location of workbook I uploaded and re-upload.

Thanks,

RE: Hide Worksheet Based on Cell Value

Sorry, I posted, from memory, the incorrect syntax for making sheets visible or not. blush

Your data begins in row 10, not row 12. The last row is 16, not 110. Furthermore, if you add additional sheets, if you hard-coded 16 as the last row, then TILT! Consequently the last row will be calculated at run time.

CODE

Sub HideUnhideSheets()
    Dim r As Range, rng As Range
    
    'set the range for cells that contain sheet names
    Set rng = Sheets("Trade List").Cells(10, "C")
    Set rng = Range(rng, rng.End(xlDown))
    
    For Each r In rng
       Select Case r.Offset(0, 2).Value
          Case 1
              Sheets(r.Value).Visible = xlSheetVisible
          Case 0
              Sheets(r.Value).Visible = xlSheetHidden
       End Select
    Next
End Sub 

Be sure that you save your workbook containing your macro as a Macro Enabled Workbook .xlsm.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hide Worksheet Based on Cell Value

Hello,

The code you have runs when you activate the macro. If you want the code to run when you enter a 0 or a 1 in column E then you can use the code below.

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Selection, Range("E12:E110")) Is Nothing Then Exit Sub
        MY_SHEET = Range(Target.Address).Offset(0, -2).Value
        If Target.Value = 1 Then
            Sheets(MY_SHEET).Visible = True
        Else
            Sheets(MY_SHEET).Visible = False
        End If
End Sub 

This code needs to go into the Trade List code window, not a standard module. The code can also be modified to select cells beyond row 110.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

RE: Hide Worksheet Based on Cell Value

There's actually a problem with using E10:E110, if a change is made in that range that has no data...

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MY_SHEET As String, rng As Range
    
    'set the range for cells that contain sheet switch values
    Set rng = Sheets("Trade List").Cells(10, "E")
    Set rng = Range(rng, rng.End(xlDown))
    
    If Not Intersect(Target, rng) Is Nothing Then
        MY_SHEET = Target.Offset(0, -2).Value
        If Target.Value = 1 Then
            Sheets(MY_SHEET).Visible = xlSheetVisible
        Else
            Sheets(MY_SHEET).Visible = xlSheetHidden
        End If
    End If
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Hide Worksheet Based on Cell Value

Forgot to say that the code does not take into account any error checking. It will also fail if the sheet names do not exactly match the list in the Trade List spreadsheet.

In your code, doesn't the set rng code cause problems if there are gaps in Column E?

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MY_SHEET As String, rng As Range
    Set rng = Range("E10:E" & Range("E" & Rows.Count).End(xlUp).Row)
    If Not Intersect(Target, rng) Is Nothing Then
        MY_SHEET = Target.Offset(0, -2).Value
        If Target.Value = 1 Then
            Sheets(MY_SHEET).Visible = True
        Else
            Sheets(MY_SHEET).Visible = False
        End If
    End If
End Sub 

This code needs to go into the Trade List code window, not a standard module.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close