Pop-up menu/index

I don't think this is really possible. Why not create a button/menu on the command bar which does what you want?

Hi h,

Do you realise that you can do this by right-clicking the navigation arrows in the bottom left corner?

Norie said:

I don't think this is really possible.

Click to expand...

Yes it is possible, and here is how.

While the built-in method that Richie pointed out should suffice, it includes the name of the sheet you are on, which you say you do not want. To get around that, and have a right-click pop-up menu appear with sheet names excluding the one you are on, the following steps would accomplish that.

Place the following code in the workbook module. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module.

Private Sub Workbook_Activate()
Application.CommandBars("Cell").Reset
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Cell").Reset
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cb As CommandBar, i As Integer
Set cb = Application.CommandBars("Cell")
With cb
.Reset
Dim cbc As CommandBarControl
For Each cbc In Application.CommandBars("cell").Controls
cbc.Visible = False
Next cbc
For i = 1 To Sheets.Count
If ActiveSheet.Name <> Sheets(i).Name Then
With .Controls.Add(Type:=msoControlButton)
.OnAction = "GoToSheet"
.Caption = Sheets(i).Name
.TooltipText = "Goto this worksheet: " & Sheets(i).Name
End With
End If
Next
End With
Set cb = Nothing
End Sub


While you are in the VBE, place the following macro in a standard module. After that, press Alt+Q to return to the worksheet.

Sub GoToSheet()
Dim acb As CommandBarButton
Set acb = Application.CommandBars.ActionControl
Sheets(acb.Caption).Select
Set acb = Nothing
End Sub

Nice one, I didn't think of using one of the existing shortcut menus. Also, would it not be better to leave in the original commands.

i.e remove

For Each cbc In Application.CommandBars("cell").Controls
cbc.Visible = False
Next cbc


Or add With .Controls.Add(Type:=msoControlButton, Before:=1) so sheet navigation commands are at the top.

I had the impression that the way the pop-up ended up looking when it appears, with only the sheet names, is the way it was expected to be for the convenience of the user. There are other ways to do this, including caption buttons, fly-out menus, etc, but this way has the least amount of visual clutter for the user. I could be wrong with how I read the intent of the end result. Post whatever you think would work better so hemsleysut can have other options.