Way To Display Worksheet Tabs In Multiple Rows

AJ1

New Member
Joined
May 18, 2006
Messages
5
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
May 18, 2006
Messages
5
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

Legend
Joined
May 15, 2003
Messages
29,536
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
 

Forum statistics

Threads
1,137,063
Messages
5,679,401
Members
419,825
Latest member
MegastarMagus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top