VBA to unhide sheets based on cell contents

[djl0525]

This code below works great to unhide sheets in my workbook.

Sub ShowExerciseSheets()
'This macro is run from the Welcome sheet
'It will unhide the Intro sheet, 6 exercise sheets and the blank sheet
'Comment out the sheets do not want to unhide


Application.ScreenUpdating = False
MsgBox ThisWorkbook.Sheets("MsgBoxes").Range("B2").Value, , "Training."
Sheet05.Visible = True 'unhide Introduction
Sheet10.Visible = True 'unhide 1st exercise sheet
Sheet20.Visible = True 'unhide 2nd exercise sheet
Sheet30.Visible = True 'unhide 3rd exercise sheet
Sheet40.Visible = True 'unhide 4th exercise sheet
Sheet50.Visible = True 'unhide 5th exercise sheet
Sheet60.Visible = True 'unhide 6th exercise sheet
Sheet99.Visible = True 'unhide blank sheet


'Activate the Introduction! Sheet
Worksheets("Introduction").Activate
Range("A1").Select


Application.ScreenUpdating = True


End Sub

Instead of opening the VB Editor to comment out sheets I don't want to display, I'd like the code to look at one of my sheets to see which sheets to unhide.

Sheetnames - The sheets are listed in columns A on the "Trainer's Sheet 1" sheet. The sheet names listed are the actual tab names (not Sheet05, Sheet10, etc.)

Which sheets to display - Column B contains Yes or No. Yes for unhide. No for do not unhide.

I'd like help modifying the code to unhide sheets where it finds Yes in column B next to the respective sheetname.

Any assistance will be greatly appreciated!

DJ

 

[mumps]

Maybe:

Sub HideSheets()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Sheets("Trainer's Sheet 1").Range("B2:B" & LastRow)
        If rng = "Yes" Then
            Sheets(rng.Offset(0, -1).Value).Visible = True
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[djl0525]

Nice work! And thank you for the quick reply!

It reads Yes very well. If column B says Yes and the sheet is hidden, the code displays it.

But if the sheet is already displayed and column B says No, the code does not hide* the sheet.

* My sheets are initially Very Hidden so I'd like the code to make their visibility Very Hidden if column B says No.

DJ

 

[mumps]

Try:

Sub HideSheets()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Sheets("Trainer's Sheet 1").Range("B2:B" & LastRow)
        If rng = "Yes" Then
            Sheets(rng.Offset(0, -1).Value).Visible = True
        ElseIf rng = "No" Then
            Sheets(rng.Offset(0, -1).Value).Visible = xlSheetVeryHidden
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[djl0525]

Hmmm... It's stopping on this line

Sheets(rng.Offset(0, -1).Value).Visible = xlSheetVeryHidden

 

[djl0525]

I am not aware of what changed, but it's working now. Must've been user error the first time.

Thank you soooooo much!!!!

DJ

 

[mumps]

You are very welcome.

 

[JCalamis]

I just joined and i'm not sure I'm in the right spot, but hear goes:
I have a 500 sheet workbook. All data entry in on the first page, and tracks the status of 1 office file per line. Each line as a pulldown entry for active, closed, on hold, , etc.
I created a separate sheet for each line, which allows a chronological entry of activities for each line, by date and note.

When a line is flagged as closed, or inactive, I want the tab for the related line hidden, If its flagged as open or active, I want it shown. I've been searching throughout the forums and I'm totally confused, as I'm not well versed on VB. Any help would be appreciated

 

[mumps]

Hello JCalamis and welcome to the Forum. Forum rules state that you should not post your question in another person's thread. Please start a new thread, describing in detail what you want to do. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data. You could include a link to this thread if you feel it may be useful.

 

[JCalamis]

Thanks for the information! I reviewed the instructions on the site, but I still can't find anything saying "Add a Thread". The only button is to "Reply to Thread"??? Can you give me any directions or give me a link with directions? What am I doing wrong?