Creating a dynamic Popup Menu to navigate tabs/sheets

[Grimtyler]

I have searched this forum and others looking for a solution to this issue. As i was unable to find something that met my needs I thought that I would post my question here.

As this is my first post if I have missed posting some vital information that you need please let me know.

I am trying to create a dynamic popup menu. I am using Excel 2010 and have found code on the net that has lead me in the right direction until now.

I am able to create a popup menu that lists all the tabs/sheets in the workbook, however when I click on any of the menu options nothing happens, it should take you to the tab/sheet you selected (I have a copy of the workbook I could upload but I can work out how to do it).

Below is the code used to make the popup menu the problem is with .OnAction any help would be greatly appreciated

Option Explicit


Public Const Mname As String = "MyPopUpMenu"


Sub DeletePopUpMenu()
    'Delete PopUp menu if it exist
    On Error Resume Next
    Application.CommandBars(Mname).Delete
    On Error GoTo 0
End Sub


Sub CreateDisplayPopUpMenu()
    'Delete PopUp menu if it exist
    Call DeletePopUpMenu


    'Create the PopUpmenu
    Dim CurSheet As String
    
    CurSheet = ActiveSheet.name
    
    Application.ScreenUpdating = False
    Call Custom_PopUpMenu_1
    Sheets(CurSheet).Select
    Application.ScreenUpdating = True


    'Show the PopUp menu
    On Error Resume Next
    Application.CommandBars(Mname).ShowPopup
    On Error GoTo 0
End Sub


Private Sub Workbook_Deactivate()
    Call DeletePopUpMenu
End Sub


Sub Custom_PopUpMenu_1()
    Dim MenuItem As CommandBarPopup
    Dim sht      As Worksheet
    
    'Add PopUp items to the menu
    With Application.CommandBars.Add(name:=Mname, Position:=msoBarPopup, _
                                     MenuBar:=False, Temporary:=True)
        
        For Each sht In ActiveWorkbook.Sheets
                
            With .Controls.Add(Type:=msoControlButton)
                    
                If sht.name <> "Index" And sht.name <> "Lists" And sht.name <> "Company Name" Then
                    .Caption = sht.name
                    ' Would like OnAction to be set to what ever the current Sheet is
                    .OnAction = "sht.name"
                End If
                    
            End With
        
        Next sht
            
    End With
    
End Sub

 

[tonyyy]

Grimtyler,

Welcome to the forum!

.OnAction = "sht.name"

The .OnAction property directs a button click to run the procedure "sht.name"... and since "sht.name" is an invalid procedure name - it contains a period - I changed the line to:

.OnAction = "GoToSheet"

And added this small procedure:

Sub GoToSheet()
Worksheets(Application.CommandBars.ActionControl.Caption).Activate
End Sub

Hope this helps.

tonyyy

 

[Grimtyler]

tonyyy,

Thanks very much for you help, your solution performed exactly as I wanted.

Would you be able to recommend a resource for learning VBA for excel ? I know that I could just do a search on Google but a personal recommendation form someone with experience is always better.

Again thanks for your help, it looks easy when someone that knows what they are doing shows you how

 

[tonyyy]

You're welcome, Grimtyler. Glad that worked for you.

Two vba books I suggest are "Excel 2010 Power Programming with VBA" by John Walkenbach (I'm pretty sure it's been updated since) and "Excel 2007 VBA, Programmer's Reference" by John Green, Stephen Bullen, Rob Bovey and Michael Alexander.

And of course there's the extensive reference list compiled by @hiker95.

 

[Grimtyler]

Thank you tonny, I will have a look the books and the reference list by @hiker95 (which at first glance is huge).