Obtain value of a control on a shortcut menu

[Guest]

Could someone please show example code on how to capture what the selected
content (a string) of a combobox was on a shortcut menu when it closed?

Thanks in advance.

 

[Tom Ogilvy]

From Rob Bovey:

Sub HandleCombo()

Dim ctlComboBox As CommandBarComboBox
Dim szItem As String

Set ctlComboBox = CommandBars.ActionControl

szItem = ctlComboBox.Text

''' This is required to overcome a screen refresh bug in Excel 97.
Application.ScreenUpdating = False
Application.ScreenUpdating = True

MsgBox "You chose " & szItem

End Sub

 

[Guest]

Hello Tom,

In my case, there are several controls on the shortcut menu of which this
particular combo box is one. Sorry I didn't make this clear earlier.

I can't seem to crack how to use "ActionControl" and yet reference the
specific control I'm after. In this case you could call it "Combo1" which is
the 5th control on the menu, and this is not the LAST control that would have
been clicked.

Do have any clue on this? Thanks again.

 

[Tom Ogilvy]

Actioncontrol is the control that triggered the macro. Use actioncontrol
exactly as shown if this is going to be the onaction property of the
control.

If not, then you would address the control explicitly just like any other
control.

commandbards("Custom 1").controls("Combo1").Text