How do I hide worksheets based on combo box selection?

Posted on 2008-11-05
Last Modified: 2013-11-25
I have a workbook with 42 worksheets. Worksheets 6-35 to be hidden. They are grouped in 3's. So the first group of three sheets will be named "1 - XXXXX". The second group will be "2 - XXXXX".

On the first worksheet in the book, users are asked to select a number from a combo box (1-10). If the user selects "1", sheets 6-8 should unhide. If the user selects "2", sheets 6-11 should unhide. If the user selects "3", sheets 6-14 should unhide. etc..

Also, If the user has selected "7", for example, and changes the number to "3", the respective sheets should be re-hidden.

Thanks in advance for any help.
Question by:Big0191
    LVL 18

    Accepted Solution

    I answered a similar question a few days ago.

    You can accomplish this will a simple macro attached to the combo box.  If the combo box is from the Forms toolbar, just right click and select Assign Macro...

    If it is from the Control Toolbox toolbar, then inside of the Change() property for the combo box you need to have the macro or a call to the macro.

    I would use a Select Case statement with the value of the combo box being what the Case is testing.
    If you provide an example of the workbook it would be much easier to provide a working example of a solution, but the link above to the other question should get you on the right track.

    Basically, you will need to adjust the Visible property of the sheets you want to Hide/UnHide.

    LVL 19

    Assisted Solution

    Three macros.  First, Workbook_Open() will load the combobox with the correct integers to use for the number of sheets in the workbook.  It also hides any sheets that should start out hidden.
    2nd, fill_combobox is the procedure called by Workbook_Open() to actually load the combobox integers.
    3rd, the combobox1_change() sub will hide or unhide the sheets according to the selection.

    Private Sub Workbook_Open()
    ' This runs only once when the workbook opens.  It makes sure sheets are hidden to start.
    ' Place this code in ThisWorkBook module.
        Dim idx As Integer
        For idx = 6 To Worksheets.Count
            Worksheets(idx).Visible = False
    End Sub
    Sub fill_combobox()
    ' sets combobox to correct entries.  Place this code in Module1.  If the combobox is not 
    ' on the first sheet then fix the Worksheets(1) reference below.
        Dim wks_ctrl As ComboBox
        Dim idx As Integer
        Set wks_ctrl = Worksheets(1).ComboBox1
        For idx = 1 To Int((Worksheets.Count - 5) / 3)
            wks_ctrl.AddItem Format(idx, "##")
        If wks_ctrl.ListCount * 3 < Worksheets.Count - 5 Then
            wks_ctrl.AddItem Format(idx, "##")
        End If
        wks_ctrl.ListIndex = -1
    End Sub
    Private Sub ComboBox1_Change()
    ' event code that runs when combobox value changes. Hides and unhides sheets.
    'Place this code in the Sheet Module that has the combobox on it.  
        Dim idx As Integer
        If ComboBox1.ListIndex = -1 Then Exit Sub   'this keeps the combobox from running as the workbook opens.
        For idx = 6 To Worksheets.Count
            If idx <= (ComboBox1.Value * 3) + 5 Then
                Worksheets(idx).Visible = True
                Worksheets(idx).Visible = False
            End If
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now