Slight modifications to a great little Macro for Tab Unhiding/Selection

I have this great little App. that epaclm produced and when I put it into production, I have a problem;

1.) It apparently picks up ALL Tabs and displays them in the drop down box where the original data elements (Industry Names are).  I need to maintain the drop down box without new additions being added unless I put them in the drop down target list (not new tabs).
2.) I need to display the hidden tab but not "go to" it.

I think these are perhaps simple corrections.

Thank you in advance,

B.
Simple-Tab-Selection-mod2.xlsm
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Curt LindstromCommented:
Hi B,

So if you select "Banking" you want to reveal the sheet but stay on the "Selection" sheet?

When you select the "Telco" you want to reveal the sheet but stay on the "Selection" sheet and so on?

So once you have selected all sheets they have all been revealed. What do you want to happen after that?

Curt
Bright01Author Commented:
Curt,

Thanks for responding.  So if you select the appropriate Industry from the dropdown list, the Tab is displayed (so yes, select Banking, and Banking is revealed), but the cursor does not go to the sheet.  If I reset the industry cell, the tab goes away (is hidden again). If I select another Industry without resetting, than both tabs will display and so on.  The reset comes with clearing the selection field and they are all hidden again.  Right now the selection is picking up all tabs and not just the selected "list".

Thank you!

B.
Curt LindstromCommented:
Hi B,

Is this what you want?

Select a tab it is revealed. Select the same tab again and it is hidden.

I have commented out a lot of of code that can be deleted if this is what you want. The button on sheet becomes nearly obsolete. All it does is to return you to the "Selection" sheet.

The sheets in the drop down are limited by counting the first 5 sheets only so if you want more you would have to change the count. A better option may be to get rid of the Worksheet_SelectionChange macro all together and simply fix the sheets you want in the data validation. The macro is not really necessary if you don't want to pick up the sheets automatically.

Cheers,
Curt
Simple-Tab-Selection-mod3.xls
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Bright01Author Commented:
Curt,

Wait.  Select the first element/industry, the appropriate tab is revealed.  Select the same element again, nothing happens (because it's already reveiled).  Select a different element/industry, the appropriate tab is ALSO revealed so now two industry tabs are revealed.  And so on......  I will then have a button that clears the selection cell as a reset.  When it's cleared, then the specific tabs ( I have others that are not impacted) are hidden again.   The return buttons don't need to be there and I can remove it.  Does that make sense?

Thank you,

B.

Curt LindstromCommented:
Ok, how about this one. Only one macro left. Validation is set to the 5 sheets. If you want to ad more or change the names you easily do that by changing the data validation list for B5.

Select one of the 5 sheets and it is revealed and stays revealed but not selected. At any time when you want to hide all sheets select through B5, just clear B5 and all of the 5 sheets that are open will be hidden.

Curt
Simple-Tab-Selection-mod4.xls
Bright01Author Commented:
Curt,

ALMOST PERFECT.  Here's the smalll problem.  When I clear the cell that indicates the Industry, it doesn't clear the TAB.  Is there a slight change to the code that recognizes a blank to the cell that would clear the TAB?

B.
Curt LindstromCommented:
What do you mean by "the cell that indicates the Industry"?

The only code left is this code which can be found under the "Selection" sheet. This code does, as you say, recognize that B5 s empty and will hide the sheets specified by the Case statement. The validation list for B5 is done directly in Data Validation (see attached picture).

Do you want to pick up the data validation from a list? It was never done like that in the original file you got from EE. I noticed that you have lists sitting to the right in the "Selection" sheet but they have not been used by the macros that was in the file when I first started to look at this. It can easily be done like that if this is what you want.


Curt
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    If Target.Address = Range("B5").Address Then
        On Error Resume Next
        If Target.Value = "" Then

            For Each ws In ThisWorkbook.Worksheets
                Select Case ws.Name
                Case "Banking", "Telco", "Retail", "Utility", "Mfg."
                    Sheets(ws.Name).Visible = False
                End Select
            Next ws

        End If
        Sheets(Target.Value).Visible = True
        On Error GoTo 0
    End If
End Sub

Open in new window

Data-Validation.bmp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Great solution. I'm certain it will be enhanced but Curt did an outstanding job on this!  

Thanks for the hard work.  It's great to see EE situations that are tough and people like Curt step up to getting it done.

Best regards,

B.
Curt LindstromCommented:
Thanks for the kind comments!

I have a different version which is more flexible.

This one has a dynamic Named range "TabList" which starts on N5. you can add or remove sheets from the list as you like. The sheet must exist to appear when you select it. No need to modify the data validation since it will always pick the values from your list starting at N5.

The code under the "Select" sheet now looks like this:

Cheers,
Curt
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim TabToHide As Variant
    If Target.Address = Range("B5").Address Then
        On Error Resume Next
        If Target.Value = "" Then

            For Each ws In ThisWorkbook.Worksheets
                TabToHide = Application.Match(ws.Name, Range("TabList"), 0)
                If IsNumeric(TabToHide) Then
                    Sheets(ws.Name).Visible = False
                End If
            Next ws

        End If
        Sheets(Target.Value).Visible = True
        On Error GoTo 0
    End If
End Sub

Open in new window

Simple-Tab-Selection-mod5.xls
Bright01Author Commented:
This looks like a nice "eligant" enhancement.  If I have 5 other Tabs in my Workbook, how does it know what to put in the Tablist?  

B.
Curt LindstromCommented:
Just add the new tab names to the end of the current list in column N. Don't move this list without updating the Named range. As long as it starts in N5 you can add and remove as many tabs you like to/from the end of the list. The data validation of B5 "knows" what tabs to add from simply referring to the Named range. The Named range is dynamic and will pick up anything added to the end.

Curt
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.