Custom file menu used to redirect to desired sheet



I want to create a file menu that contains a submenu that lists all sheets in
the workbook and when a sheet is selected it is subsequently activated.

I've created the list and alphabetized it.
I've also created all menus. Current the path is Look At Me > List of
Sheets > (all sheets listed).

what I need to have happen is when a sheet name is selected it then needs to
be activated. I can't seem to figure out how to capture which sheet was
selected from the menu list.

See my code below. CreateMenu and RemoveMenu are called from the
workbook_activate and _deactivate events. The JumpToSheet macro would be
where I would handle the activation of the selected sheet.


Sub CreateMenu()
Dim HelpIndex As Integer
Dim NewMenu As CommandBarPopup
Dim sh As Worksheet

''make list of sheets
Sheets("Sheet1").Range(Range("A2"), Range("A2").End(xlDown)).ClearContents
For Each sh In Worksheets
Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0).Value =
Next sh
HelpIndex = CommandBars(1).Controls("Help").Index

Set NewMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, _
Before:=HelpIndex, Temporary:=True)
NewMenu.Caption = "&Look at me"

''sub menu - Jump to Sheet...
Set Item = CommandBars(1).Controls("Look at
Item.Caption = "Jump to sheet..."

''Sub of Jump to Sheet
For Each cell In Range(Range("A2"), Range("A2").End(xlDown))
'Set Item = CommandBars(1).Controls("Look at me").Controls("Jump to
With Item.Controls.Add(Type:=msoControlButton)
If cell <> "" Then
.Caption = cell.Value
.OnAction = "JumpToSheet"
End If
End With
Next cell
End Sub

Sub RemoveMenu()
On Error Resume Next
Application.CommandBars(1).Controls("Look at me").Delete
Application.CommandBars(2).Controls("look at me").Delete
End Sub

Sub JumpToSheet(SheetName)
End Sub


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question