Excel VBA - return the value of a caption of a CommandBarControl (trying to create an addin)

[Harry Flashman]

Can anyone tell me if it is possible to return the caption from a CommandBarControl with VBA? Thus if the user clicks the CommandBarControl a message box could appear containing the caption of that CommandBarControl.

I am trying to create an Excel add-in that I can use to track my time working on various projects: a time-tracker.

I have created an Excel add-in with a range of menu items (Type:=msoControlPopup). Those menu items are created when the add-in load by reading off the values on a worksheet within the addin. Those values represent the projects I want to track.

I want to take the caption from each menu item and use that caption as a variable in my code.
For example the caption for the first menu item might be “Admin”. Thus when the user click the “Admin” menu item, that value is passed to come code which then writes “Admin” into my time-tracker , along with a time-stamp

Presently I have a clumsy and inflexible workaround. Each menu item calls a separate routine which contains the name of the menu item. This works for now, but it is not very flexible. When I update the worksheet within the add-in that contains the values for the captions (and other information) to a different set of values (that is projects I want to track) I then need to change the constants in my code (containing the name for the project whose value I want to record).

But if I could just take the value from the caption of the menu item and pass that to code, I would easily be able to update the worksheet in the add-in, without having to change any code.

I hope this makes sense. Does anyone understand what I am talking about.

Here is a snippet of the code I am using to create the menu items. This code reads the value of the caption off a worksheet, when the addin is being created (upon starting Excel). This is just to give you an idea of how the menu items are created.

' loop through each row of worksheet reading the values and creating menu itmes
' [...]
With myMenuItem.Controls.Add(Type:=msoControlButton)
    .Caption = menuSheet.Cells(RowNo, 2).Value
    .OnAction = menuSheet.Cells(RowNo, 3).Value
    .BeginGroup = menuSheet.Cells(RowNo, 4).Value
    .FaceId = menuSheet.Cells(RowNo, 5).Value
End With
'[...]
'loop