Way To Display Worksheet Tabs In Multiple Rows

[AJ1]

I'm not sure if Excel is this customizable as of yet, but I'm using Excel 2003 and I have a main Worksheet that is fed data from 20+ other worksheets which are all tabbed at the bottom of the main worksheet. Rather than scrolling right each time I need to get to one of the worksheets, I'd like to be able to stack the worksheets in rows on top of eachother. So for example, say I have 27 worksheets. Rather than scroll right every time I want to see the 28th worksheet, I want to display 3 rows of 9 that I can see at all times and not have to scroll. Is this possible? If so, how can I enable this?

thanks!

AJ

 

[Richard Schollar]

Hi AJ

I don't know of any way to do that I'm afraid, but maybe someone else will.

Do you know that, instead of using the worksheet tab scroll buttons, you can right-click on them to bring up a dialog where you can select the specific sheet you want? It is marginally more convenient than scrolling thru all the worksheets.

Best regards

Richard

 

[Andrew Poulsom]

That's not possible, but if your right click on the buttons to the left of the sheet tabs you will get a popup to select from.

 

[AJ1]

It sounds like Excel just isn't that flexible as of yet. Thanks for the info. I did know about the popup, but once again, it's about having it all in one view and it doesn't sound like that's possible.

 

[Smitty]

Welcome to the Board!

Zack Baresse (aka Firefytr) has some code to create a table of contents with hyperlinks to each sheet:

<font face=Tahoma><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> CreateTOC()
    <SPAN style="color:#007F00">'   Code by Zack Baresse</SPAN>
    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> Application
        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
        .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
    
        <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, _
            ct <SPAN style="color:#00007F">As</SPAN> Chart, _
            shtName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _
            nrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
            tmpCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
            i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
            numCharts <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
        
        nrow = 3
        i = 1
        numCharts = ActiveWorkbook.Charts.Count
        
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> hasSheet
        Sheets("TOC").Activate
        <SPAN style="color:#00007F">If</SPAN> MsgBox("You already have a Table of Contents page.  Would you like to overwrite it?", _
        vbYesNo + vbQuestion, "Replace TOC page?") = vbYes <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> createNew
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

hasSheet:
    Sheets.Add Before:=Sheets(1)
    <SPAN style="color:#00007F">GoTo</SPAN> hasNew

createNew:
    Sheets("TOC").Delete
    <SPAN style="color:#00007F">GoTo</SPAN> hasSheet

hasNew:
    tmpCount = ActiveWorkbook.Charts.Count
    <SPAN style="color:#00007F">If</SPAN> tmpCount > 0 <SPAN style="color:#00007F">Then</SPAN> tmpCount = 1
        ActiveSheet.Name = "TOC"
        
        <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")
            .Cells.Interior.Color<SPAN style="color:#00007F">In</SPAN>dex = 4
                <SPAN style="color:#00007F">With</SPAN> .Range("B2")
                    .Value = "Table of Contents"
                    .Font.Bold = <SPAN style="color:#00007F">True</SPAN>
                    .Font.Name = "Tahoma"
                    .Font.Size = "24"
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
            nrow = nrow + 1
            <SPAN style="color:#00007F">With</SPAN> ws
                shtName = ws.Name
                <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")
                    .Range("B" & nrow).Value = nrow - 3
                    .Range("C" & nrow).Hyperlinks.Add _
                        Anchor:=Sheets("TOC").Range("C" & nrow), Address:="#'" & _
                        shtName & "'!A1", TextToDisplay:=shtName
                    .Range("C" & nrow).HorizontalAlignment = xlLeft
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> ws
        
        <SPAN style="color:#00007F">If</SPAN> numCharts <> 0 <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ct In ActiveWorkbook.Charts
                nrow = nrow + 1
                shtName = ct.Name
                <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")
                    .Range("B" & nrow).Value = nrow - 3
                    .Range("C" & nrow).Value = shtName
                    .Range("C" & nrow).HorizontalAlignment = xlLeft
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> ct
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        
        <SPAN style="color:#00007F">With</SPAN> Sheets("TOC")
            <SPAN style="color:#00007F">With</SPAN> .Range("B2:G2")
                .MergeCells = <SPAN style="color:#00007F">True</SPAN>
                .HorizontalAlignment = xlLeft
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
            <SPAN style="color:#00007F">With</SPAN> .Range("C:C")
                .EntireColumn.AutoFit
                .Activate
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            .Range("B4").Select
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
        .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    MsgBox "Done!" & vbNewLine & vbNewLine & "Please note: " & _
        "Charts are listed after regular " & vbCrLf & _
        "worksheets and will not have hyperlinks.", vbInformation, "Complete!"

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty