Worksheet Tabs, Relocating?

0 views
Skip to first unread message

Jack Trexler

unread,
Apr 15, 2003, 10:35:21 AM4/15/03
to
Is there anyway to have the worksheet tabs listed down the
right side of the window rather than the bottom? With
multiple worksheets, it becomes difficult to remember
where the worksheet tabs are located. There may be other
solutions to this problem and if so, I would be interested
in them.

David McRitchie

unread,
Apr 15, 2003, 11:05:02 AM4/15/03
to
Hi Jack,
No, tabs at bottom or no tabs is the only choice.
Unless you have a strange monitor the horizontal lineup

There are some things that might help you with tabs.
Sorting them with a macro (SortAllSheets)
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets
The basic purpose of the buildtoc.htm page is to
Build a Table of Contents.

You can right-click on any of the worksheet navigation arrows to see the
worksheet names -- works better if sheet tabs have been sorted.
If you have a few sheets you will be forces to use "more sheets",
you can use a macro get there immediately.

Sub SheetList_CP()
'Chip Pearson, 2002-10-29, misc., 23ByZYZ3fCHA.1308%40tkmsftngp11
On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
End Sub

More information on worksheets
http://www.mvps.org/dmcritchie/excel/sheets.htm
---
HTH, The searches at the top of my search.htm have been changed
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jack Trexler" <Tre...@eastman.com> wrote in message news:034801c3035c$3e646fb0$3301...@phx.gbl...

David McRitchie

unread,
Apr 15, 2003, 12:09:08 PM4/15/03
to
> Unless you have a strange monitor the horizontal lineup

sorry for extraneous wording, realised afterwards that horizontal
names arranged one above the other veritcally could show more
sheet tabs in a full screen view. But it is not possible in Excel. and the usual
problem is to show as much of a row as possible so I wouldn't think
it would be viable solution but I think there are packages to do allow
docking to anywhere and probably mutiple layers, for me sorting
worksheet tabipl.
--

HTH, The searches at the top of my search.htm have been changed
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]

Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David McRitchie" <dmcri...@msn.com> wrote in message

Shah Shailesh

unread,
Apr 16, 2003, 8:55:58 AM4/16/03
to
Hi David McRitchie,

>You can right-click on any of the worksheet navigation
arrows to see the
>worksheet names -- works better if sheet tabs have been
sorted.
>If you have a few sheets you will be forces to use "more
sheets",
>you can use a macro get there immediately.
>
>Sub SheetList_CP()
> 'Chip Pearson, 2002-10-29, misc., 23ByZYZ3fCHA.1308%
40tkmsftngp11
> On Error Resume Next
> Application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
>End Sub
>


Here is another three method to show sheet activate
dialog. Might be you knew it. Just for your notice.

1. Find control & execute.

Sub moresheets()
CommandBars.FindControl(Type:= _
msoControlButton, ID:=957).Execute
End Sub

2. Run this macro to add control to standard toolbar &
afterword you have to click this button to activate dialog.

Sub AddControl()
'add button to commandbar for sheet activate dialogbox.

Set cnt = CommandBars("Standard").Controls.Add _
(Type:=msoControlButton, before:=1, ID:=957)

End Sub

3. Using sendkeys: More then 16 sheet then show activate
dialogbox else popup sheet name.

Sub SheetActivate()
If Application.CommandBars("workbook tabs").Controls
(16).Caption Like "More Sheets*" Then
Application.SendKeys "{end}~"

Application.CommandBars("workbook tabs").ShowPopup

End Sub


With Regards,
Shah Shailesh

David McRitchie

unread,
Apr 16, 2003, 6:50:10 PM4/16/03
to
Hi Shah,
I'll keep your comments in mind. If I recall correctly the person who
originally asked the question was strictly interested in assigning a
keyboard shortcut so his fingers never leave the keyboard.

"Shah Shailesh" <shahsh...@hotmail.com> wrote ...

Reply all
Reply to author
Forward
0 new messages