Results 1 to 8 of 8

Show Sheet Tabs in two rows

This is a discussion on Show Sheet Tabs in two rows within the Excel Questions forums, part of the Question Forums category; Hi Everybody, I have a workbook containing so many worksheets that not all the sheet tabs can be visible at ...

  1. #1
    Board Regular Fergus's Avatar
    Join Date
    Mar 2004
    Location
    Algeria (pro-tem)
    Posts
    1,162

    Default Show Sheet Tabs in two rows

    Hi Everybody,

    I have a workbook containing so many worksheets that not all the sheet tabs can be visible at the same time. I have already shortened the sheet names as much as possible. Does anyone know a way of showing the sheet tabs in two rows?
    Cheers, Fergus.
    using Win XP Sp3 & Excel 2003;
    struggling with Win 7 Pro & Excel 2007

  2. #2
    New Member
    Join Date
    Jun 2005
    Location
    Derby
    Posts
    33

    Default

    Just a suggestion, but something i have done for our work equipment list is place a new worksheet as a front page and on it creat hyperlinks in columns down the page - these will link to the worksheet required, then rename the cell, next you want it to go back to the main page so create on each worksheet a hyperlink back to this main page. You could even have these other sheets hyperlinked down the side of each wsheet to make it even easier to navigate.

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    24,832

    Default

    Fergus

    Don't know about showing the tabs on two rows but something else that may be of some use to you is:

    To the left of the sheet tabs are four arrows for navigating through the tabs. If you right click in this arrow area, you will see a list of up to about 15 sheets at once. You can click on one of these sheet names to jump straight to the sheet.

    If you have more sheets than about 15, at the bottom of the list will be a choice 'More Sheets...'. Click on this and you will have a list of all the sheets in your work book with a scroll bar to navigate to the one you want.

    Not perfect, but I do find it useful.

    Good luck.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  4. #4
    Board Regular Fergus's Avatar
    Join Date
    Mar 2004
    Location
    Algeria (pro-tem)
    Posts
    1,162

    Default

    Peter and Neuro, thanks for the good ideas, both useful but not quite what I was after. What I have is one very large table on Sheet1 containing stock data which is updated every day. This contains prices (Opening, High, Low and Closing) for about 30 stocks. The following sheets are graphs of each stock performance, and there are more sheets than there is room for tabs!
    What I was wishfully thinking was to be able to quickly switch back and forth between the input table and the various charts without even having to scroll along the bottom run of tabs. Just being lazy really. Your various, worthy, suggestions don't make it any quicker than what is already provided by Gates, but all the same, thanks for the effort. I had also considered splitting the data into two workbooks, but for other reasons it is convenient to keep it in one.
    Cheers, Fergus.
    using Win XP Sp3 & Excel 2003;
    struggling with Win 7 Pro & Excel 2007

  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default

    Hello Fergus:
    If your stock names are the same as the sheet names then the following code would take you to the Sheet by just double clicking on the StockName.

    ASSUMPTIONS:
    1. Stock names match sheet names
    2. Following code pasted into the sheet that lists all stocks
    3. Stock names are in column 2 of the sheet that lists all stocks

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 2 Then
    On Error Resume Next
    Sheets(Target.Value).Select
    Cancel = True
    End If
    End Sub
    TO INSTALL IN SHEET CODE WINDOW:
    1. Select the sheet you want code to work in
    2. right click the the "Name Tab" of sheet
    3. Select "View Code" in drop down menu
    4. VBE window will open ... paste code in and exit VBE


    NOTE: To then go back to original master sheet just record a macro while doing so. Then assign macro to short-cut key . Done !

    Now to goto specfic Stock sheet just click on it's name on master. To go back to master just hit the designated short cut key .

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  6. #6
    Board Regular Fergus's Avatar
    Join Date
    Mar 2004
    Location
    Algeria (pro-tem)
    Posts
    1,162

    Default

    Hi Nimrod,

    Thankyou for that good idea. My sheet names are indeed the same as the stock codes. Only difference is my stock data is organised in columns across the table, with a single column of sequential dates in column A. But that just meant changing the target to a Row number instead of a Column number. Now it works great and does the trick, for which many thanks.
    Cheers, Fergus.
    using Win XP Sp3 & Excel 2003;
    struggling with Win 7 Pro & Excel 2007

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default

    Hello Fergus
    Glad you liked the idea , did you install the 2nd part as well ... e.g. creating a macro to take you back to the master page ?

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  8. #8
    Board Regular Fergus's Avatar
    Join Date
    Mar 2004
    Location
    Algeria (pro-tem)
    Posts
    1,162

    Default

    Hi Nimrod,

    Yes indeed I did. As you suggested I just recorded a macro with a shortcut key. makes a very quick and short one-line macro, see below, but it works great, so thanks a lot.
    Code:
    Sub GoBackToTable()
    ' GoBackToTable Macro
    ' Macro recorded 21/6/2005 by Fergus Ferguson
    ' Keyboard Shortcut: Ctrl+t
    Sheets("Table").Select
    End Sub
    Cheers
    Cheers, Fergus.
    using Win XP Sp3 & Excel 2003;
    struggling with Win 7 Pro & Excel 2007

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com