Articles Blog

Show Specific Sheets in Excel With Drop Down List

Show Specific Sheets in Excel With Drop Down List


In this workbook, we have quite a few worksheets and I’ve set up a drop down list that we can use to hide everything except a
specific kind of worksheet. I’ll show you how that works and then we can look at the code
quickly in the background. This is the menu sheet and there’s a cell here that has a drop down list. If I click that, I can
see, Computer, P&L and ALL. I’ll click on Computer
and now we just have the menu sheet showing and the two sheets that had computer in their names. I’ll do the same for P&L, so there are the two sheets that are
P&L and now I’ll go back to looking at all the sheets
and all the sheets, the admin sheets and instructions and everything is visible now. To set this up, we have a data validation drop down list here, if
I go to the data tab, data validation, we can
see that there’s a list and it’s based on sheet types. So I’ll close that and that sheet types is on this admin list, I’ve just entered three items here, I typed all and I left a space character at
the beginning of that, just so if I sort this list, that item will always go up to the
top, so it’s easy to find. Then once the list was
entered on this sheet, I created a table from
that, so on the home tab, you can format as a table and select one of these styles for
it and that means that now if I put another item
at the bottom of this list, the list will expand automatically, we can see that the list is
now grown and I can sort this. And now if I go back to the menu, we’ve got admin and that just shows any sheet that has admin in its name. So it’s a quick way to
work with your sheets if you want to either
see a whole lot of them or just a small selection of your sheets so you can focus on those. The sheets that are visible, change because a macro runs, if we
make a change in this cell and to see that code, I’m gonna
right click on the menu tab, view code, and here is the
worksheet change event. So it’s gonna look at the target and that’s the changed cell. If the address of that cell is the same as the select type address and if we look at this cell, and look up in the name box here, that
cell is named selecttype. If we’ve changed that
cell, then we’re going to see what the value of the target cell is. If we select it all, you
can see this space there, it’s gonna run a macro
called, showallsheets. If we just clear out that cell, then nothing’s gonna happen, we can’t show sheets if we don’t have the text that’s in the name of those sheets. And, if they didn’t select all or the cell isn’t empty,
then it’s case else, so for every other situation, we’re going to run a macro called, ShowSelSheets. To see those macros, I’m
going to right click here, definition, and that takes
us here into module 1 and we can see the ShowAllSheets and there’s a variable,
ws, which is a worksheet. So for each worksheet in
the active workbook sheets, that worksheet, visible
property is visible. So every sheet becomes visible and for the other macro, ShowSelSheets, we again have a variable for the worksheet and one for the string, the str type, so whatever type we’ve
selected here on the menu sheet in that select type range,
so whatever that value is, that’s what we’re going to be looking for. For each worksheet, in
the ActiveWorkbook sheets, if we can find that name, so the strType in the worksheet name and we’re using the InStr, the in string function for that, so if it does find that
string in the name, then the sheet is visible, otherwise, unless the sheet is named, menu, we wanna leave that visible,
then that sheet will be hidden. Those are the two different macros that will run based on
making a change here. For more Excel tips and tutorials and to download the sample
file for this video, please visit my contextures
website at www.contextures.com

18 thoughts on “Show Specific Sheets in Excel With Drop Down List”

  1. I'm looking to unhide sheets in Excel using an Input Box instead of the Drop-down list. Below is the code I have so far, however I want to modify the macro to use an Input Box to choose the specific text to look for. Can someone point me in the direction either to a video or open-source code?

    Sub Unhide_Sheets_Containing()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    If InStr(ws.Name, "pivot") > 0 Then
    ws.Visible = xlSheetVisible
    End If
    Next ws

    End Sub

  2. Hallo. I have written a macro to automatically add and rename worksheets by clicking a button in a Userform. I want the combo box in the Userform to automatically add worksheets names in the dropdown list when I create them so that I can run the macro to hide or unhide the selected sheet from the combo box drop down list. How can I do it?

  3. what if i have two drop down list..and besides menu sheet visible, what if menu sheet and instructions sheet both of these make two sheets make visible?what should i type or add in macro? thx..

  4. A great video.  I followed the instructions and can see all the tabs or none of the tabs but cannot select individual tabs to view.  I compared my macros with the macros in UF0034 and they are identical except for the tab names.  What am I doing wrong?

  5. Siddeek from KSA, Please help me in this movement so this will help for many of them. I hope In Excel VBA can do that when ever opening excel application there has to open user form that user form consist Name ID No and login time while finishing work logout time has to enter automatically in a specific folder. further all the details should be hidden only allowed to see supervisor and that folder has to consist a password as well and the sheet has to be veiled I hope you won't distinguish this request Thank and God bless you Siddeek

  6. thanks for the video!
    I am wondering if I want the sheet dropdown list in a main sheet(once I open the file, main sheet always is active sheet), dropdown list includes all other sheets, once I select one sheet from the dropdown sheet(main sheet), I would like that sheet is active sheet, so I could edit it . for example, I have 5 sheets , from the dropdown list on main menu, I could see 5 sheets' name as sheet1, sheet2,…., if I select sheet1, the active sheet will become sheet1, so I could edit any data on the sheet1.
    from the each sheet#, I could also go back to make sheet which has dropdown sheet list.
    same thing, once I get one sheet, there is dropdown list for different areas, I could select any area to process.

    thanks
    rose

  7. Hi, I know this is an old post but really handy macro code. Anyways, what if the worksheets are protected and would like to unprotect them when I selet sheet type/list? Been searching for specific code but no luck. hope you can still help me or any one there have idea.

    Thanks.

  8. Is something like this possible but with certain excel tables? I mean, that I would choose a value in the dropdown list and it would show me a certain table, that was not visible before.

Leave a Reply

Your email address will not be published. Required fields are marked *