Show Sheet Tabs in two rows

[Fergus]

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?

 

[neuro49]

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.

 

[Peter_SSs]

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.

 

[Fergus]

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.

 

[Nimrod]

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

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 .

 

[Fergus]

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.

 

[Nimrod]

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 ?

 

[Fergus]

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.

Sub GoBackToTable()
' GoBackToTable Macro
' Macro recorded 21/6/2005 by Fergus Ferguson
' Keyboard Shortcut: Ctrl+t
Sheets("Table").Select
End Sub

Cheers

 

[My Aswer Is This]

A lot of nice tricks here I learned.