[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

How do I hide worksheets based on combo box selection?

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.
2 Solutions
Cory VandenbergSenior Risk ManagerCommented:
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.

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


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now