Thread: add button to right-click menu in External Data range

I'd like to add a button to the top of the right-click (context) menu. I know how to do this with CommandBars("Cells"), CommandBars("Row") and CommandBars("Column").

But how do I do this when the range contains data from an external source?

I can add a button to CommandBars("External Data") in the same way as with the other CommandBars, but it doesn't actually appear. And the buttons in this CommandBar are only the ones in the bottom section of the pop-up menu. The top half looks like the regular "Cells" menu.

How does this stuff work?

...

A separate question. All the right-click menu examples I've seen use the BeforeRightClick event. Why not just use the Activate event? Is there a functional advantage with BeforeRightClick? In the Activate event, it wouldn't have to run as often.

Hello dlh,

It isn't clear to me what you are trying to do. If you know how to add a button to a menu then what problems are you experiencing?

Hello dlh,

Which version of Excel are you coding this in 2003 or 2007? In 2003 the "External Data" menu is not a context menu.

I'm developing in 2003. But I need this to work in 2007 also.

For what it's worth, I've confirmed that my button was successfully added to the CommandBar. But it doesn't appear on screen.

Hello dlh,

It would help to see the code you have written. Can you post it?

Hello dlh,

The button is there, you just need to give it a FacecID. The menu entries like File, Edit, etc. are Popup menus. That's why they appear as labels. Button controls require an icon (FacecID) to be visible.

Hello dlh,

This code adds a generic FaceID for running a macro...

Code:

Sub TestA()

 Dim CustomMenuItem As Object
 
    Set CustomMenuItem = Application.CommandBars("External Data").Controls.Add(Type:=msoControlButton, Before:=1, Temporary:=True)
    
      With CustomMenuItem
        .Caption = "&My Macro"
        .FaceId = 2151
        .OnAction = ""
        .Tag = "dlh-custom"
      End With

   Application.CommandBars("External Data").Visible = True

End Sub

Hello dlh,

I only see one CommandBar named "External Data". It could be that another menu attaches the "External Data" menu to itself. Here is a macro I wrote to list all the CommandBars along with other information about them. It may help you track down what you are looking for.

Code:

'Written: May 31, 2008
'Updated: May 18, 2010
'Author:  Leith Ross
'Summary: Lists all the avaiable menus in Excel on a workhseet. It provides the
'         menu name, whether it is visible, the type, and if it is enabled.

Sub ListExcelMenus()

  Dim arr As Variant
  Dim C As Variant
  Dim CmdBar As Object
  Dim R As Long
  Dim T As String
  
    C = "A"
    R = 1
    
      With Range("A1:E1")
        .Value = Array("Name", "Visible", "Type", "Enabled", "ID")
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
      End With
      
        For I = 1 To Excel.CommandBars.Count
          Set CmdBar = Excel.CommandBars(I)
          With CmdBar
            Select Case .Type
              Case 0
                T = "Normal"
              Case 1
                T = "Menu"
              Case 2
                T = "Popup"
            End Select
            arr = Array(.Name, .Visible, T, .Enabled, .ID)
            Cells(R + I, C).Resize(1, 5).Value = arr
          End With
        Next I
      
       Range("A:E").Columns.AutoFit
       
End Sub

Hello dlh,

Glad you found it. Thanks for sharing the solution.