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.
Big0191Asked:
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.

Cory VandenbergSenior Risk ManagerCommented:
I answered a similar question a few days ago.  
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23870761.html

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.

WC
0

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
folderolCommented:
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
    fill_combobox
    For idx = 6 To Worksheets.Count
        Worksheets(idx).Visible = False
    Next
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, "##")
    Next
    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
        Else
            Worksheets(idx).Visible = False
        End If
    Next
End Sub

Open in new window

0
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
Visual Basic Classic

From novice to tech pro — start learning today.