Getting data from a custom menu item

[kbanes]

I'm just starting to use VBA, and I have created a custom toolbar that contains a combo box. I want to open a form based on the contents of the combo box.

My question is, how do I address the menu combo box object?



The Combo box is a list system numbers. I want to open a form containing system information that matches the system that I selected in the combo box.

I'm very confused, and I have probably done the same to you, but I really need some help.

 

[RickSpr]

First, add the Microsoft Office 9.0 Object Library (8.0 for Access 97) to your References. Then use the MS Office Object Model objects to access your toolbar (a CommandBar object). You can then get your custom control (a CommandBarComboBox object) by accessing the Controls collection of the toolbar.

    Dim cbr As CommandBar, cbc As CommandBarComboBox
    Set cbr = CommandBars("your toolbar name")
    Set cbc = cbr.Controls("your combo box caption")
    strSomething = cbc.Text

The Application.CommandBars property (referred to as just "CommandBars" above) is a collection of all command bars--menu bars, toolbars, and shortcut menus--defined in the application.

There's actually a shortcut, if you want to get to the custom toolbar button from code that was called by its Action property setting. In this case, you can just refer to Commandbars.ActionControl.

    Dim cbc As CommandBarComboBox
    Set cbc = CommandBars.ActionControl
    strSomething = cbc.Text

Rick Sprague