On my copy of XL2003 (and this works in XL2007), I get the same popup menu
as this SheetBeforeRightClick event procedure produces when I right-click
the navigation controls (the four little buttons) to the left of the Sheet
Tabs.

Rick


"Mark Ivey" wrote in message
...
John,

I really like the function you listed, but I also use my right click menu
extensively....

Do you have something like this that doesn't do away with the rest of the
features of a right click menu?

Mark Ivey

"john" wrote in message
...
A simple way would be to use the builtin popup.
Paste followin code into Thisworkbook page. When you right click any
worksheet, you will get a menu of all worksheet tabs in activeworkbook.
Click
to select required worksheet.

Hope useful.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
Target
As Range, Cancel As Boolean)
On Error Resume Next
If ActiveWorkbook.Sheets.Count <= 16 Then
application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
On Error GoTo 0
Cancel = True
End Sub
--
jb


"Petr Danes" wrote:

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

Good point Rick...

I always new this feature was there, but I usually forget about it when I
really need it...


One reason I made the addin I mentioned previously in this thread was
because I usually have several workbooks open at the same time and need to
navigate not only to the workbook, but also to a specific worksheet.

Mark

"Rick Rothstein (MVP - VB)" wrote in
message ...
On my copy of XL2003 (and this works in XL2007), I get the same popup menu
as this SheetBeforeRightClick event procedure produces when I right-click
the navigation controls (the four little buttons) to the left of the Sheet
Tabs.

Rick


"Mark Ivey" wrote in message
...
John,

I really like the function you listed, but I also use my right click menu
extensively....

Do you have something like this that doesn't do away with the rest of the
features of a right click menu?

Mark Ivey

"john" wrote in message
...
A simple way would be to use the builtin popup.
Paste followin code into Thisworkbook page. When you right click any
worksheet, you will get a menu of all worksheet tabs in activeworkbook.
Click
to select required worksheet.

Hope useful.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
Target
As Range, Cancel As Boolean)
On Error Resume Next
If ActiveWorkbook.Sheets.Count <= 16 Then
application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
On Error GoTo 0
Cancel = True
End Sub
--
jb


"Petr Danes" wrote:

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

Didn't know about that one, Rick, thanks for the tip. Unfortunately, it's
limited to 15 sheet names before you have to use the "More sheets..."
feature to access stuff further to the right, which is right around the
number where the need for a direct jump function starts rearing its head.
And then you get a ListBox with scroll bars, which is even more clicking and
fiddling. It's still useful even so - I wonder if there is a reg hack that
would force Excel to show more than 15 names.

Petr





"Rick Rothstein (MVP - VB)" pÃ*Å¡e v
diskusnÃ*m pÅ™Ã*spÄ›vku ...
On my copy of XL2003 (and this works in XL2007), I get the same popup menu
as this SheetBeforeRightClick event procedure produces when I right-click
the navigation controls (the four little buttons) to the left of the Sheet
Tabs.

Rick


"Mark Ivey" wrote in message
...
John,

I really like the function you listed, but I also use my right click menu
extensively....

Do you have something like this that doesn't do away with the rest of the
features of a right click menu?

Mark Ivey

"john" wrote in message
...
A simple way would be to use the builtin popup.
Paste followin code into Thisworkbook page. When you right click any
worksheet, you will get a menu of all worksheet tabs in activeworkbook.
Click
to select required worksheet.

Hope useful.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
Target
As Range, Cancel As Boolean)
On Error Resume Next
If ActiveWorkbook.Sheets.Count <= 16 Then
application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
On Error GoTo 0
Cancel = True
End Sub
--
jb


"Petr Danes" wrote:

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

Wow - I never knew that was there! Thanks Rick, most useful!

Regards

Chris

On Apr 21, 3:10*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
On my copy of XL2003 (and this works in XL2007), I get the same popup menu
as this SheetBeforeRightClick event procedure produces when I right-click
the navigation controls (the four little buttons) to the left of the Sheet
Tabs.

Rick