Custom shortcut Menu with a Popup Control showing data-driven item

[RossW]

My goal is to have a custom shortcut menu associated with a form control
which will let users right-click and select an item from a list which is
derived from records in a table. The problem so far is how do I detect and
react when a user selects an item?

I've got code written which creates a new shortcut menu and adds a
msoControlPopup control which in turn has msoControlButton controls added to
it based on records in a table. I can't use the OnAction property for these
items (I don't think) since that property doesn't take input parameters and I
need to know which item they selected. Is it possible to assign the same
OnAction function to each item in the popup and then write code to detect
which button was clicked, or do I need a WithEvents?

Any help would be greatly appreciated.

 

[mcescher]

My goal is to have a custom shortcut menu associated with a form control
which will let users right-click and select an item from a list which is
derived from records in a table. The problem so far is how do I detect and
react when a user selects an item?

I've got code written which creates a new shortcut menu and adds a
msoControlPopup control which in turn has msoControlButton controls addedto
it based on records in a table.  I can't use the OnAction property for these
items (I don't think) since that property doesn't take input parameters and I
need to know which item they selected. Is it possible to assign the same
OnAction function to each item in the popup and then write code to detect
which button was clicked, or do I need a WithEvents?

Any help would be greatly appreciated.

Create your menu as a macro, then create the main menu as a second
macro that uses an AddMenu action that points to the first macro.
Then as you look at the properties of the entire form (not just the
detail), enter the name of the main menu macro as the "Shortcut Menu
Bar", and the save.

This will add your right-click menu for the entire form. You could
also have individual menus for different objects on the form. Most of
them should have a Shortcut Menu Bar property. This would give you
context sensitive menus.

I've probably not done the best job explaining this, but I'm sure if
you search your help file for AddMenu and Shortcut Menu Bar, you
should get a better picture. It's only hard the first time.

Hope this helps,
Chris M.

 

[RossW]

Hi Chris,

The problem is that my sub-items need to be queried from the database at
runtime - I think your method wouldn't allow me to do that, nor can I see how
I can code the response to the sub-item selection. I'm not very familiar with
creating menus through macros, so maybe I'm missing something.

Ross

 

[RossW]

I just found this code in the Excel section:

Application.CommandBars.ActionControl.Caption

So, all I had to do was assign the same OnAction function to each item in
the popup and when a user selects an item the above command returns which one
they selected. Pefrect!