Need to access non-native menu commands in VBA

**jkupfer** · 01-02-2009, 04:06 PM

Hi All,

I have an interesting problem I have not been able to find any references to anywhere, so here I am....

I'm using Excel 2003 that has a menu bar added by another program that interfaces with it (via DDE, as I understand it). The DLL's from the other program have no documentation and/or ability for me to connect to the particular drop-down menu command I'm using. It is a refresh command that re-imports stock market trade data because DDE drops trade messages.

Anyway, I hope I've explained that clearly enough. I'm just trying to figure out how I can call the command for a non-native menu bar item. You can't record a macro to access it. Surely there is some way. Any other ideas?

Thanks in advance.

**rylo** · 01-02-2009, 04:36 PM

Hi

Does this thing add an addin, or a reference in the VBE?

rylo

**jkupfer** · 01-02-2009, 04:45 PM

No, neither unfortunately.

**shg** · 01-02-2009, 05:11 PM

Andy Pope recently posted this code to list all command bar controls:

Sub ListControlIDs()
    Dim oCBC    As CommandBarControl
    Dim iRow    As Long
    Dim iID     As Long

    On Error Resume Next
    For iID = 1 To 31308
        Set oCBC = Application.CommandBars.FindControl(ID:=iID)
        If Not oCBC Is Nothing Then
            iRow = iRow + 1
            Cells(iRow, 1).Select
            Cells(iRow, 1) = iID
            Cells(iRow, 2) = oCBC.Caption
            Cells(iRow, 3) = oCBC.Parent.Name
        End If
    Next
End Sub

Once you find it in the list, you can execute it. For example, type this in the Immediate window:

Application.CommandBars.FindControl(ID:=1849).Execute

**jkupfer** · 01-02-2009, 05:28 PM

Great idea, and very close, but no cigar.

Here are the first few lines of what I get. #1 Sterling Trader Pro is actually the name of the menu bar that has the refresh command I need to access, but the actual command (nor the other commands in that menu bar) are listed anywhere in the list generated by that macro.

1	Sterling Trader Pro	Worksheet Menu Bar
2	&Spelling...	Standard
3	&Save	Standard
4	&Print...	Document
18	&New...	Desktop
19	&Copy	Standard
21	Cu&t	Standard
22	&Paste	Standard
23	Open	Standard
37	&Repeat Find	Edit
47	Clear	Button
106	&Close	File
107	Forma&t Report...	PivotTable Context Menu
108	&Format Painter	Standard
109	Print Pre&view	Standard
113	&Bold	Formatting
114	&Italic	Formatting
115	&Underline	Formatting
120	Align &Left	Formatting
121	Align &Right	Formatting