Creating a dynamic Popup Menu to navigate tabs/sheets

Grimtyler

New Member
Joined
Aug 3, 2016
Messages
3
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

Code:
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Grimtyler,

Welcome to the forum!

Code:
.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:

Code:
.OnAction = "GoToSheet"

And added this small procedure:

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

Hope this helps.

tonyyy
 

Grimtyler

New Member
Joined
Aug 3, 2016
Messages
3
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 :eek::LOL:
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
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

New Member
Joined
Aug 3, 2016
Messages
3
Thank you tonny, I will have a look the books and the reference list by @hiker95 (which at first glance is huge).
 

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,585
Members
414,460
Latest member
uctc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top