Thanks Mark, but that has more functionality than is appropriate for my
task. I only need to navigate around the one workbook, and the extra
fiddling of opening the UserForm, clicking in one ListBox, then clicking in
another ListBox, then finally clicking a CommandButton is more effort than I
want to expend. With the menu approach, I press the mouse button on the menu
bar caption to open the menu, slide down to the sheet name I want and let go
of the button. I find it difficult to imagine anything simpler.

Your approach looks good for navigating around multiple workbooks, though,
and I will keep it in mind for such an eventuality. One suggestion, though,
if you don't mind. If you add a double-click event handler to the second
ListBox, you could dispense with the OK button, or at least have it as an
alternative. Maybe a double-click event handler would even be appropriate
for the first ListBox, with some default action, like jumping to the first
sheet in the workbook, or maybe the most recently used sheet in the workbook
if you want to get creative. (My apologies if it already does stuff like
that. I didn't download it, just looked at the website.)

Petr




"Mark Ivey" pí¹e v diskusním pøíspìvku
...
See if something like this may better suit your needs....

http://www.graceba.net/~wmivey/Workb..._navigator.htm

If so, I have also developed this into an addin... you can download a copy
of it here...

http://www.graceba.net/~wmivey/Workb...gator/Workbook Navigator.xla


Let me know if this was helpful... I would like to hear any feedback you
have for me.

Mark Ivey


"Petr Danes" wrote in message
...
I recently started teaching in a software technical school, and my first
course was in advanced Excel. Naturally, I prepared a workbook with lots
of examples on various worksheets, but not until I started lecturing did
I realize that lots of sheets mean lots of scrolling left and right to
locate the correct sheet. I'd never needed a workbook with many sheets
for any of my own projects, nor those I had created for others. The
course went fairly well anyway, but I wanted a better way to navigate
between sheets.

Part of the idea came from ASAP Utilities' function of creating a
separate index sheet with clickable hyperlinks for all sheets, and part
of the code from John Walkenbach's Excel Power Programming book. (My
thanks to you both.)

The clickable index sheet function is handy, but solves only half of the
problem - it quickly locates a sheet 'somewhere', but getting back to the
index sheet for another shot out into the myriad of sheets is still a
hassle. I decided a menu bar approach suited my needs better.

The code reads in all the sheet names and creates a dropdown menu of
sheet names. Originally it included a sort as well, but then I decided
that I prefer the menu in the same order as the sheets in the workbook,
and my naming and ordering scheme results in the names being in order
anyway. The sort is still there, just the call commented out, in case
someone wants to use it that way. There is no error checking and it takes
no account of hidden properties of sheets, since I don't use that, but it
could be added easily enough. The code is called from the Workbook_Open
routine, so that the menu is freshly rebuilt every time the workbook
opens. Probably not completely necessary, but the execution time is
trivial and it makes for ease of use. I initially added a call from the
Workbook_NewSheet event, but abandoned that since the code adds the
default sheet name in use at the instant of creation (Sheet1 or some
such), which is not very useful, since I always rename the sheet to
something sensible, but by then the sheet has already been added to the
menu with default name. If there were Workbook_SheetRename and
Workbook_SheetDelete events, I would add calls there, as well as the
NewSheet event. That would make it completely automatic and always
current, but this works well enough. The first item on the menu, before
the sheet names is a refresh call, so I can keep the menu up to date
manually without having to close and re-open the workbook or switch to
the VBA editor.

There are two versions of the menu code, one creates a simple dropdown
menu list, suitable for up to approximately thirty sheets on my machine
(a bigger monitor would allow more), the second creates a two-stage list,
grouped by the first character in the sheet name. I name my sheets by
course day, so that "1.n xxxxx" is a sheet for the first day of the
course, "2.n xxxxx" for the second day and so on. "0.n xxxxx" sheets are
general sheets, not intended for any specific day. The second method
obviously better handles a very large number of sheets, at the cost of a
fraction of a second delay while the second tier unfolds. At the moment,
my code creates both versions. I'll have to use them for a while to see
which one I prefer for classroom use.

Pete

--
This e-mail address is fake, to keep spammers and their auto-harvesters
out
of my hair. If you want to get in touch personally, I am 'pdanes' and I
use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.



Start code (watch for line wrap!)
================================================== ==

Option Explicit

Dim AEb As CommandBarButton
Dim AEp As CommandBarPopup
Dim AEp2 As CommandBarPopup
Dim SheetNames() As String

Sub SheetMenu()
Dim i&

ReDim SheetNames(1 To ActiveWorkbook.Sheets.Count)

' Fill array with sheet names
For i = 1 To UBound(SheetNames)
SheetNames(i) = Sheets(i).Name
Next i

' Sort the array in ascending order
' Not used in current implementation,
' but simply uncomment line below to order sheet names.
'BubbleSort SheetNames

' Build the sheet menus
MakeMenuItem ' simple menu
MakeMenuItem2 ' 2-tier menu

End Sub

Private Sub MakeMenuItem()
' Create the menu bar addition
Dim i&
With Application.CommandBars("Worksheet Menu Bar")
On Error Resume Next
.Controls("Sheets").Delete
On Error GoTo 0
Set AEp = .Controls.Add(msoControlPopup)
With AEp
.Caption = "Sheets"
Set AEb = .Controls.Add(msoControlButton)
With AEb
.Caption = "(...Refresh...)"
.OnAction = "GoToSheet"
.Parameter = "(...Refresh...)"
End With
For i = 1 To UBound(SheetNames)
Set AEb = .Controls.Add(msoControlButton)
With AEb
.Caption = SheetNames(i)
.OnAction = "GoToSheet"
.Parameter = SheetNames(i)
End With
Next i
End With
End With
End Sub

Private Sub MakeMenuItem2()
' Create the menu bar addition
Dim i&, nmhld$

' Only one character is used for grouping
nmhld = "!"
With Application.CommandBars("Worksheet Menu Bar")
On Error Resume Next
.Controls("Sheets2").Delete
On Error GoTo 0
Set AEp = .Controls.Add(msoControlPopup)
With AEp
.Caption = "Sheets2"
Set AEb = .Controls.Add(msoControlButton)
With AEb
.Caption = "(...Refresh...)"
.OnAction = "GoToSheet"
.Parameter = "(...Refresh...)"
End With
For i = 1 To UBound(SheetNames)
If nmhld < Left$(SheetNames(i), Len(nmhld)) Then
Set AEp2 = .Controls.Add(msoControlPopup)
AEp2.Caption = Left$(SheetNames(i), InStr(SheetNames(i),
"."))
nmhld = Left$(SheetNames(i), Len(nmhld))
End If
With AEp2
Set AEb = .Controls.Add(msoControlButton)
With AEb
.Caption = SheetNames(i)
.OnAction = "GoToSheet"
.Parameter = SheetNames(i)
End With
End With
Next i
End With
End With
End Sub

Sub BubbleSort(List() As String)
' Sorts the List array in ascending order
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp

First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If List(i) List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i
End Sub

Public Sub GoToSheet()
' Action routine called by the menu
' Passed parameter is either the sheet name to call or the refresh
command
If CommandBars.ActionControl.Parameter = "(...Refresh...)" Then
SheetMenu
Else
ActiveWorkbook.Sheets(CommandBars.ActionControl.Pa rameter).Activate
End If
End Sub