Tab Groups

Scott wishes:

G’day Dick,

My wish list would be to have the functionality to group excel tabs within a workbook. So instead of having 20+ tabs going along the bottom of the screen you could group (similar to TabScrip in VBA ) them into a common theme. Maybe group all the raw data tabs, input sheets, output reports, State summaries, Yearly reports, etc.

Now, if you have some finance VBA code that already does it……then here’s your chance to make a wish come true. ?

Cheers matey.

I couldn’t find a picture of TabScrip, and if he meant TabStrip, then I don’t know what he’s talking about. You can color your tabs to group them. You can’t move the tabs to any other position except the bottom, as far as I know.

I have a few workbooks that have a lot of tabs. In those rare situations I wish for better navigation of tabs. If you right click on the VCR buttons to the left of the tabs, you can quickly select a tab. If you have more than 16 tabs, you can’t see them all. Instead you get a “More Sheets” options that brings up an unsortable dialog.

First, I’d like that right click list to go all the way to the top of the screen. Don’t stop at 16, stop at 30 or some other number that gives me more options. If I do need to open the dialog, I want three sort options: the order they appear in the workook; alphabetical order; and zOrder (the order in which they were last accessed). I guess that’s six sorts with ascending and descending options. I don’t really need this because I only have a handful of really big workbooks. But it would be handy in those cases.

What do you think about Scott’s wish or tabs in general?

Posted in Uncategorized

16 thoughts on “Tab Groups

  1. I did something similar some time ago:
    Here’s hoping the code looks correct after HTML munching.

    Assuming you have numbered sheets, eg.
    “1 Intro”, “1 Specs”, “2 Design”, “2 Detail”, “2 Process”, “3 Implementation”, “4 Deploy”, “4 Post-Deploy”, “5 Go Live”, “5 Go Live (2)”, “5 Go Live (3)”, “6 Support”, “6 Post Go Live Issues”, “Sheet3?

    Paste this to the code module for ThisWorkbook:

    Private Sub Workbook_Activate()
        Toolbar_ON
    End Sub
     
    Private Sub Workbook_Deactivate()
        Toolbar_OFF
    End Sub

    Paste this to a Standard Code Module, then run Toolbar_ON

    Const cCommandBar = “GroupedSheets”
     
    Sub Toolbar_OFF()
        Dim bar As CommandBar
     
    ”’ Delete the Commandbar if it already exists
       For Each bar In Application.CommandBars
            If bar.Name = cCommandBar Then bar.Delete
        Next
    End Sub
     
    Sub Toolbar_ON()
        Dim bar As CommandBar, wks As Worksheet, ctl As CommandBarPopup
        Dim i As Long, arrGroups(0 To 11) As CommandBarControl
     
        Toolbar_OFF
     
        Set bar = Application.CommandBars.Add(Name:=cCommandBar, Position:=msoBarBottom, Temporary:=True)
        bar.Visible = True
     
        For Each wks In ThisWorkbook.Worksheets
            i = SheetNameToGroupNum(wks.Name)
            If arrGroups(i) Is Nothing Then
                Set ctl = bar.Controls.Add(Type:=msoControlPopup)
                If i = 0 Then ctl.Caption = “Other” Else ctl.Caption = ”    “ & i & ”    “
                Set arrGroups(i) = ctl
            Else
                Set ctl = arrGroups(i)
            End If
     
            With ctl.Controls.Add(Type:=msoControlButton)
                .Caption = wks.Name
                .OnAction = ThisWorkbook.Name & “!Popup_Click”
                .Parameter = wks.Name
            End With
        Next
     
    End Sub
     
    Function SheetNameToGroupNum(SheetName As String) As Long
        Dim i As Long
     
        i = 1
        For i = 1 To Len(SheetName)
            If Not IsNumeric(Mid(SheetName, i, 1)) Then Exit For
        Next
        If i = 1 Then i = 0 Else i = Left(SheetName, i – 1)
     
        If i  10 Then i = 0
        SheetNameToGroupNum = i
    End Function
     
    Sub Popup_Click()
        With Application.CommandBars.ActionControl
            ThisWorkbook.Worksheets(.Parameter).Activate
        End With
    End Sub
  2. Old good times when using Excel 4 or 5 (well, it was a long time ago). When working with workbooks you could use an index sheet. It covered all page /screen area, you could use about 20-30 characters per sheet name (it was wow then) and you could see the whole name. Copying and moving was very comfortable, perhaps it was kind of grouping.
    Dick writes: “If you have more than 16 tabs, you can’t see them all.” I don’t remember how many items were visible on 14? display, but it was very satisfactory. Now it would be more.

    Rob – I get syntax error. Perhaps due to copy-paste from web page.

  3. You have to replace the & with “&”
    At the end you need to insert an equal sign in the code line If i = 10 then i = 0

  4. Wow Rob- that’s great…

    am8421 – after pasting you need to change:
    If i 10 Then i = 0
    to
    If i = 10 Then i = 0

    and change all the & to just &

  5. I’ve got a free workbook structure add-in here:
    http://www.codematic.net/excel-tools/free-excel-tools.htm
    it adds an index sheet with a list of sheets and descriptions and facility to group them into multiple groups
    Its a bit agricultural, but I’ve found it useful for big workbooks.
    There is quicknav there too – that adds a toolbar with a drop down list of sheets and names for easy navigation. (that too is a bit agricultural – anyone see a pattern?)
    cheers
    Simon

  6. I can confirm that all of the tools posted here work in Excel 2007. The tool bars appear as a menu on the Add-ins tab, which is maybe not ideal, but better than nothing.

    They all have their good and bad points. I think I’m most likely to use Tushar’s, if any, because it seems to be the simplest and easiest to implement.

    The add-in at robbo.com.au looks like an impressive application, but it will take time to investigate all that it does.

    By the way, Rob Van Gelder, what has happened to your web site? I just get random adverts when I click on your name.

  7. My website is hosted as a free homepage. My ISP recently forced adverts onto the page.
    I must do something about redesigning it soon, or the adverts will take over :(

  8. Rob – the advert is all I get.

    Just a single ad for My Space Jobs, which takes you to the My Space Jobs site if you click on it.

    Nothing else at all.

  9. Thanks all for help with VBA corrections. It works now, nice tool for navigating. Rob – your website in Firefox 2 is OK, in IE 6 – only adverts are displayed. Simon – I’m not sure if I see an agricultural pattern in add-in, it’s rather modest in colours and patterns. Most of users I work with love to use multiple colours for cell fills, fonts and individually for some values (like a set of textmarkers, absolutely not conditional formatting). Worksheets look like you had hallucination.

  10. I made the following, which is messy because I’m super new at this. Basically it lists and sorts all the tabs in a spreadsheet in ascending or descending order then creates hyperlinks to each on an index page, as well as using the A1 cell in each spreadsheet to link back to the index page. Let me know how terrible it is for a more or less first-timer!

    Sub SheetSorter()

    ‘ SheetSorter Macro
    ‘ Macro recorded 5/14/2008 by Adam Valine

    ‘ Keyboard Shortcut: Ctrl+Shift+S


    ‘Declare variables
    Dim msgResponse As Integer
    Dim intSheetCount As Integer
    Dim intRollThrough As Integer
    Dim intRowCount As Integer
    Dim msgSortOrder As Integer
    Dim intSheetOrder As Integer
    Dim intSheetName As String
    Dim strLinkAddress As String

    ‘Determine if macro has been run
    ‘If NO then create the sheet and run the sort
    If Sheets(1).Name  “111 Sort Sheet” Then
        Sheets.Add
        ActiveSheet.Name = “111 Sort Sheet”
        Sheets(“111 Sort Sheet”).Move before:=Sheets(1)

        intSheetCount = ActiveWorkbook.Sheets.Count
        intRollThrough = 1
           
    ‘Display the worksheet names
    For intRollThrough = 1 To intSheetCount

    ActiveSheet.Hyperlinks.Add Anchor:=Range(“A1”).Offset(intRollThrough – 1, 0), Address:=“”, SubAddress:=“‘” & Sheets(intRollThrough).Name & “‘!A1”, TextToDisplay:=Sheets(intRollThrough).Name

    Next intRollThrough

    ‘Define intRowCount variable
    intRowCount = Application.WorksheetFunction.CountA(Range(“A1:A65536”))

    ‘Define msgSortOrder variable
    msgSortOrder = MsgBox(“Select ‘Yes’ to run Ascending Sort, or ‘No’ to run Descending Sort”, vbYesNo)

    ‘Determine sort order of sheets
       Range(Range(“A1”), Range(“A1”).Offset(intRowCount – 1, 0)).Select
    ‘If YES then Ascending order
    If msgSortOrder = vbYes Then
        Selection.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    Else
    ‘If NO then Descending order
       Selection.Sort Key1:=Range(“A1”), Order1:=xlDescending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End If
       
    ‘Create and downfill anchor numbers, which will be used to sort the sheets
       Columns(“A:A”).Select
        Selection.Insert shift:=xlToRight
        Range(“A1”).Formula = “1”
        Range(“A2”).Formula = “2”
        Range(“A1:A2”).Select
        Selection.AutoFill Destination:=Range(Range(“A1”), Range(“A1”).Offset(intRowCount – 1, 0)), Type:=xlFillDefault
        Range(“A1”).Select

    ‘Define intSheetOrder variable
       intSheetOrder = 1
       
    ‘Define intSheetName variable

       
    For intSheetOrder = 1 To intSheetCount

    intSheetName = Range(“B1”).Offset(intSheetOrder – 1, 0)
    Sheets(intSheetName).Move before:=Sheets(intSheetOrder)
    ActiveSheet.Hyperlinks.Add Anchor:=Range(“A1”), Address:=“”, SubAddress:=“‘” & Sheets(“111 Sort Sheet”).Name & “‘!A1”, TextToDisplay:=Sheets(“111 Sort Sheet”).Name
    Sheets(“111 Sort Sheet”).Select

    Next intSheetOrder

    Sheets(“111 Sort Sheet”).Move before:=Sheets(1)
    Columns(“A:A”).Select
    Selection.Delete shift:=xlToLeft
    Range(“A1”).Select

    Else
    ‘If YES then ask the user if they want to re-run the macro
       msgResponse = MsgBox(“The worksheets are already sorted.” & Chr(13) & Chr(13) & “Would you like to delete the sort sheet and sort again?”, vbYesNo)
    ‘If YES user chooses to re-run, delete the sort sheet and re-run the sort from scratch
    If msgResponse = vbYes Then
    Sheets(“111 Sort Sheet”).Delete
        Sheets.Add
        ActiveSheet.Name = “111 Sort Sheet”
        Sheets(“111 Sort Sheet”).Move before:=Sheets(1)

        intSheetCount = ActiveWorkbook.Sheets.Count
        intRollThrough = 1
           
    ‘Display the worksheet names
    For intRollThrough = 1 To intSheetCount

    Range(“A1”).Offset(intRollThrough – 1, 0) = Sheets(intRollThrough).Name
    ActiveSheet.Hyperlinks.Add Anchor:=Range(“A1”).Offset(intRollThrough – 1, 0), Address:=“”, SubAddress:=“‘” & Sheets(intRollThrough).Name & “‘!A1”, TextToDisplay:=Sheets(intRollThrough).Name

    Next intRollThrough

    ‘Define intRowCount variable
    intRowCount = Application.WorksheetFunction.CountA(Range(“A1:A65536”))

    ‘Define msgSortOrder variable
    msgSortOrder = MsgBox(“Select ‘Yes’ to run Ascending Sort, or ‘No’ to run Descending Sort”, vbYesNo)

    ‘Determine sort order of sheets
       Range(Range(“A1”), Range(“A1”).Offset(intRowCount – 1, 0)).Select
    ‘If YES then Ascending order
    If msgSortOrder = vbYes Then
        Selection.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    Else
    ‘If NO then Descending order
       Selection.Sort Key1:=Range(“A1”), Order1:=xlDescending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End If
       
    ‘Create and downfill anchor numbers, which will be used to sort the sheets
       Columns(“A:A”).Select
        Selection.Insert shift:=xlToRight
        Range(“A1”).Formula = “1”
        Range(“A2”).Formula = “2”
        Range(“A1:A2”).Select
        Selection.AutoFill Destination:=Range(Range(“A1”), Range(“A1”).Offset(intRowCount – 1, 0)), Type:=xlFillDefault
        Range(“A1”).Select

    ‘Define intSheetOrder variable
       intSheetOrder = 1
       
    ‘Define intSheetName variable

       
    For intSheetOrder = 1 To intSheetCount

    intSheetName = Range(“B1”).Offset(intSheetOrder – 1, 0)
    Sheets(intSheetName).Move before:=Sheets(intSheetOrder)
    ActiveSheet.Hyperlinks.Add Anchor:=Range(“A1”), Address:=“”, SubAddress:=“‘” & Sheets(“111 Sort Sheet”).Name & “‘!A1”, TextToDisplay:=Sheets(“111 Sort Sheet”).Name
    Sheets(“111 Sort Sheet”).Select

    Next intSheetOrder

    Sheets(“111 Sort Sheet”).Move before:=Sheets(1)
    Columns(“A:A”).Select
    Selection.Delete shift:=xlToLeft
    Range(“A1”).Select

    Else
    ‘If NO no changes are made to the sheet or sort
    MsgBox “Your sort order will not be changed.”
    End If
    End If
       
       
    End Sub

  11. Hi, I’m a non-coder and I need what I would call tabs and sub-tabs in Excel. So the top level, or Master tabs, would be labeled A-C, D-F, etc. Then I need sub-tabs under each, with client names: Allen, Jane, Adams, Mari, Brown, Sue, etc. Each of those tabs (per client) will contain hundreds of rows for individual transactions. When my employees need to add something (or deduct) from a client’s account, they need to be able to click on, for instance, the M-N tab and see only the last names that start with those 2 letters. I can’t have a single speradsheet with 500 or more tabs (one for each client). Any help for a rookie? I found a post with some code to insert, but all tabs except the Masters were hidden – I couldn’t see the other tabs at all. Thanks for any help you can provide!

  12. What Scott has expressed a wish for is EXACTLY what I am trying to find a way to do. This would greatly improve organization for people who currently have to work with multiple workbooks in their dayly routine. It would essentially allow grouping of workbooks within a single Excel File.

  13. I am looking for the same thing.
    For instance – a workbook to track all financial data for my organization.
    Summary page shows monthly totals – and annual totals – linked to any of 6 categories which also have monthly totals. Those totals are generated by information on other worksheets – linked.

    For example – If summary (Level 1) contains Students, Loans, Food, Projects, Overhead – then Loans has it’s own level 2 worksheet – which shows all current and past loans with total borrowed, total interest owed, number of payments and total outstanding – which is linked to a separate worksheet for each loan (Level 3).

    This is a simplified expl of my workbook…so you can imagine that it gets pretty crowded with almost 35 worksheets in the workbook. I would really like to be able to group all of the Level 3s by category (level 2) i.e. have a group of “loans” tabs that hide unless open.

    Any ideas?

Leave a Reply

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