Can I keep one tab always visible?

  • Thread starter Thread starter Bon Rouge
  • Start date Start date
B

Bon Rouge

I've made an index sheet as the first sheet of a workbook. There are
200-odd pages in the workbook. As I go to one of the later sheets, the
index tab scrolls to the left and off the screen with the other
sheets.
I wonder if I could keep that tab on the screen at all times?

I appreciate any help.
 
Bon,
There is no "Freeze Tab" command in Excel.
You can use the following code to always keep the index sheet to the left of the active sheet.
It may be more confusing / aggravating then helpful, but you be the judge.
'-----------------------------------------------------------
'Put this code in the "ThisWorkbook" code module.
'Change "Sheet1" to the name of your index sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not Sh Is Worksheets("Sheet1") Then
Application.EnableEvents = False
Worksheets("Sheet1").Move before:=Sh
Sh.Activate
Application.EnableEvents = True
End If
End Sub

'-----------------------------------------------------------
Regards,
Jim Cone
San Francisco, CA
 
I don't know of any way to keep one sheet tab always visible.

However, if you right-click on the sheet navigation buttons (at the left
of the sheet tabs), you can select a sheet from the list.
 
Why not assign a short cut key Ctl-Shift-I to a macro:
Sub GotoMyIndex()
Worksheets("MyIndex").Activate
End Sub

making your MyIndex sheet a single combination-key stroke away
plus giving you "full screen" access to all sheets?

just a thought...
 
Another option:

Add a hyperlink to that one sheet on the top of each worksheet (Cell A1) and
window|freeze panes so that row 1 and column A is always visible.

=HYPERLINK("#"&CELL("address",sheet1!a1),"Click Me")

If you like that idea and you're going back to an index worksheet, maybe you
should visit David McRitchie's site.

He has code that will build a table of contents worksheet with hyperlinks to
each of the other worksheets.

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Thank you all for your comments and help, but I think I've actuall
found the best solution for me.
I simply opened up another window of the same book. As the main poin
of the index page is to jump to other sheets quickly, I can have on
window with just the list of names to the left (window 2) and the othe
window (window 1) taking most the rest of the screen. Clicking
hyperlink in window 2 opens the right sheet in window 1 (the index i
window 2 doesn't change).
After saving and then reopening the book the windows open in the sam
arrangement, so it's good.
Simple and effective.

Next question :

The problem with this arrangement (using an index with hyperlinks) i
that the sheets always open up at a specific cell (A1 by default) - I'
like to open the sheets at the last entry as they normally would open i
I just clicked the tab....
Any ideas?

I appreciate your ideas
 
Back
Top