LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 1st 18, 11:37 PM posted to microsoft.public.excel.programming
external usenet poster
First recorded activity by ExcelBanter: Oct 2016
Posts: 6
Default How to determine the name of a macro ran from the quick links menu

I have a drop down list in my quick links menu that runs different macros or opens different spreadsheets. It was from John Walkenbach or some one else. I do not remember any more as I have had it for many years now.
My question is, is there some code that will give me the name of the macro that was just called when I click on one of the list items from the quick links drop down this code created?

(Quicklinks drop down list)
List of standard part types

If I click on the list item "-Bearings" it has a macro associated to it called "Std_Parts_Bearings" and in the macro I have the link to that spreadsheet. How can I get the name of the link/menu item "-Bearings" that I clicked on that calls the "Std_Parts_Bearings" macro?


Menu Maker for the quick links bar:

Option Explicit

Sub CreateMenu()
' This sub should be executed when the workbook is opened.
' NOTE: There is no error handling in this subroutine

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup
Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

Dim myMenuCtl As CommandBarControl
Dim Counter As Integer

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
'''''''''''''''''''''''''''''''''''''''''''''''''' ''

' Make sure the menus aren't duplicated
Call DeleteMenu

' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
If IsNumeric(.Cells(Row, 3)) Then
'Find the last command bar index # if adding another command bar
For Each myMenuCtl In Application.CommandBars("Worksheet Menu Bar").Controls
Counter = myMenuCtl.Index
Next myMenuCtl
If .Cells(Row, 3) Counter Then
PositionOrMacro = Counter + 1
End If
PositionOrMacro = .Cells(Row, 3) 'Used to set Macro assignment
End If

Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Befo=PositionOrMacro, _
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId < "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId < "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
End Sub

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving 2003 Custom Menu to Quick Access Toolbar Orion Cochrane Excel Programming 1 November 30th 09 04:11 PM
a quick way to determine the first and last biz date of the month Ben[_14_] Excel Programming 3 February 20th 09 01:16 PM
How to create a standard Quick Launch menu in Excel 2007 Lasse[_2_] Excel Programming 3 October 22nd 08 03:21 PM
DP adjustment using quick menu buttons when sheet protected Aaron Excel Worksheet Functions 0 April 12th 07 02:05 AM
How to determine links to external files or spreadsheets Jim[_9_] Excel Programming 3 June 1st 06 04:56 PM

All times are GMT +1. The time now is 12:34 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"


Copyright © 2017