Amending Right-Click Options

M

Matt

Hello everyone

I've added options to the right-click menu using

With Application.CommandBars("Query").Controls.Add
(temporary:=True)
.Caption = "Download All &Transactions for this
Element"
.BeginGroup = True
.FaceId = 458
.OnAction = "DownloadAllTransactionsForThisElement"
End With

Now I'd like to remove all the default options that
appear when I right-click on a cell that is part of
a "Query", particularly the "Edit Query" and "Data Range
Properties Options". Does anyone have any ideas?

Thanks in advance

Matt
 
B

Bob Phillips

Matt,

I don't seem to have a Query commandbar, but if there is one, you should be
able to see all the control names with


For Each ctl In Application.CommandBars("Query")
Debug.Print ctl.Caption
Next ctl

THen fior each, just run


With Application.CommandBars("Query")
.Controls("name1").Visible = False
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Matt

Hi Bob

Thank you for this.
The query toolbar will appear when you right-click on any
data in query you have created using Microsoft Query.

For Each ctl In Application.CommandBars("Query")
Debug.Print ctl.Caption
Next ctl

Gives me an error message "Object does not support this
property or method." Not sure what this means?

Regards


Matt
 
B

Bob Phillips

Matt,

Sorry, I always make that mistake. Try this version

For Each ctl In Application.CommandBars("Query").Controls
Debug.Print ctl.Caption
Next ctl


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Matt

Hi Bob

That's excellent thank you

Have used ........

Dim ctl As String
Dim strCntrl As String

Application.CommandBars("Query").Reset

For Each ctl In Application.CommandBars("Query").Controls
Debug.Print ctl.Caption
strCntrl = ctl.Caption

With Application.CommandBars("Query")
.Controls(strCntrl).Visible = False
End With
Next ctl

To get rid of them all before I add my own

Thank you very much indeed

Matt
 
B

Bob Phillips

Matt,

ctl should not be declared as a string. It is either a generic Object or a
specific CommandbarControl.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top