1

Is there a way to group worksheets in Excel 2010 so that not all tabs are immediately visible? Basically, so that they could be expanded and minimized as needed.

I have a few Excel workbooks with quite a large number of tabs. And it's kind of a pain to scroll around to find a particular worksheet.

| improve this question | |
0

Here, I whipped something up, modify as you like. It could be much shorter, but you get the idea.

Insert a combobox (on a master sheet, mine is named "control"), assign it a list and then place this macro in the visual basic. Group as you like, be sure one sheet is always visible and leave a case to show all.

Sub DropDown1_Change()

With ActiveSheet.Shapes(Application.Caller)
        Select Case .ControlFormat.ListIndex

        Case 1
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetVisible
            Sheet3.Visible = xlSheetVisible
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 2
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetVisible
            Sheet5.Visible = xlSheetVisible
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 3
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetVisible
            Sheet7.Visible = xlSheetVisible
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 4
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetVisible
            Sheet9.Visible = xlSheetVisible
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 5
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetVisible
            Sheet11.Visible = xlSheetVisible

        Case 6
        For Each ws In Worksheets
        ws.Visible = xlSheetVisible

        Next
        End Select

    End With
End Sub
| improve this answer | |
  • Considering that I've never worked with macros before, how do I go about implementing this? I tried following a few guides that I found online, but I didn't end up with anything that seemed to actually work. – Force Flow Dec 29 '13 at 2:18
  • Are you getting errors? – Raystafarian Dec 29 '13 at 2:20
  • No, but I think I went a little overboard when I was trying to populate the combobox: pastebin.com/8vSZBrnz The combobox appears to be named DropDown2, though I can't seem to find a way to change that or populate it (my code doesn't work). – Force Flow Dec 29 '13 at 2:40
  • It doesn't look like you've declared the variables you're using. Also, in VBA you have to CALL other macros. – Raystafarian Dec 29 '13 at 3:03
  • In which function? I really have very little experience in VB--I'm mostly a PHP guy. So, in the initialization function, what needs to be corrected in order to populate the combobox? I made some corrections: pastebin.com/qaq4LEse – Force Flow Dec 29 '13 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.