CODEWITHSUNDEEP
excelvba
Stack
Stack

Reputation: 65

How do I enable the right-click macro for tables?

I have the following code that works fine for normal worksheets, but when I try to right click over a table the macro does not appear.

I have tried the below but getting "Object Required" error message on the first line:

With ContextMenuListRange.Controls.Add(Type:=msoControlButton, before:=2)
    .OnAction = "'" & ThisWorkbook.Name & "'!" & "macro"
    .Caption = "macro"
    .Tag = "My_Cell_Control_Tag"
End With

The below works fine with a normal worksheet.

With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
    .OnAction = "'" & ThisWorkbook.Name & "'!" & "macro"
    .Caption = "macro"
    .Tag = "My_Cell_Control_Tag"
End With

How do I get to appear when right clicking over a table?

Upvotes: 2

Views: 1286

Answers (1)

Domenic
Domenic

Reputation: 8114

Try adding your button to the List Range Popup shortcut menu...

With Application.CommandBars("List Range Popup").Controls.Add(Type:=msoControlButton, before:=2)
    .OnAction = "'" & ThisWorkbook.Name & "'!" & "macro"
    .Caption = "macro"
    .Tag = "My_Cell_Control_Tag"
End With

You can use the following macro to generate a list of all shortcut menus...

Sub ShowShortcutMenuNames()
    Dim Row As Long
    Dim cbar As CommandBar
    Row = 1
    For Each cbar In Application.CommandBars
        If cbar.Type = 2 Then 'msoBarTypePopUp
            Cells(Row, 1) = cbar.Index
            Cells(Row, 2) = cbar.Name
            Row = Row + 1
        End If
    Next cbar
End Sub

Hope this helps!

Upvotes: 7

Related Questions