problem with custom right click menu 1

[HorseGoose]

I have a query in my worksheet.

Normally when i right click on a worksheet I have code which allows me to customise the "cell" menu. This works fine everywhere.

However, when i right click on the data which is the body of the query I cannot customise the "cell" menu anymore. I imagine it is calling another menu when you right click on on data from a query but I cannot find the name of this menu to disable it. Can anyone help please?

Thanks

 

[rmikesmith]

Normally when i right click on a worksheet I have code which allows me to customise the "cell" menu.

I'm assuming by this you mean you have already customized the Cell popup menu which displays a custom menuitem whenever the worksheet is right-clicked. Is that right? When you right-click over the data area, the Query shortcut menu is displayed. I suggest not disabling it but rather altering it to include your customization and optionally, removing the querytable-specific items. Here is some example code to get you started:

Sub CustomizeQueryShortcutMenu()
Dim QTMenu As CommandBar
Dim Ctrl As CommandBarControl
Dim IndexStart
Dim i As Long


   Set QTMenu = Application.CommandBars("Query")

   [COLOR=green]' Add your custom menuitem (at the top of the menu)[/color]
   Set Ctrl = QTMenu.Controls.Add(Type:=msoControlButton, before:=1)
   With Ctrl
     .Caption = "Custom Cell Action"
     .Tag = "Query_Cell"  [COLOR=green]'Optional - can be used to locate this control later if needed[/color]
     .OnAction = "CustomCellAction" [COLOR=green]'or whatever your procedure is named[/color]
   End With

   [COLOR=green]' Disables/Hides the QueryTable functions[/color]
   With QTMenu
     Set Ctrl = .FindControl(ID:=1950)
     IndexStart = Ctrl.Index
     For i = IndexStart To .Controls.Count
       .Controls(i).Enabled = False
       .Controls(i).Visible = False
     Next i
   End With
   
End Sub


Sub ResetQueryShortcutMenu()
   Application.CommandBars("Query").Reset
End Sub

Of course, substitute your captions, procedure names, etc.

Hope this helps.
Mike

 

[firefytr]

Mike, you'll want to add a Delete procedure just before your ".Add" or else you'll keep adding the menu item again and again and again..

-----------
Regards,
Zack Barresse

 

[rmikesmith]

Mike, you'll want to add a Delete procedure just before your ".Add" or else you'll keep adding the menu item again and again and again..

Hmmm... that sounds interesting! But seriously, yes, I missed that in my haste. Should have called my reset procedure, something like:

Sub CustomizeQueryShortcutMenu()
Dim QTMenu As CommandBar
Dim Ctrl As CommandBarControl
Dim IndexStart
Dim i As Long

   [b]ResetQueryShortcutMenu[/b]
   Set QTMenu = Application.CommandBars("Query")
[COLOR=green]' Existing code here[/color]

Also, to be well-behaved code (i.e., plays well with others), I should not have simply used the Reset method, on the chance that another Add-In or other other VBA host had modified the same popup menu, as those customizations would be removed as well. Less chance of that with a shortcut menu than a Menubar or Toolbar, though.

Regards,
Mike

 

[TonyJollans]

Zack,

Good to see you here

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[firefytr]

Hiya Tony! Thanks! Heard about the forums here but never really checked them out. Hope I can help some people around here and learn some things along the way.

-----------
Regards,
Zack Barresse