asked on

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

Curt Lindstrom

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

Bright01

ASKER

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 Lindstrom

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

Bright01

ASKER

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 Lindstrom

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

Bright01

ASKER

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.

ASKER CERTIFIED SOLUTION

Curt Lindstrom

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bright01

ASKER

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 Lindstrom

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

Bright01

ASKER

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 Lindstrom

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