Greetings.... I have created a right-click menu in Excel as follows:
With Application.CommandBars("Cell").Controls.Add(msoCo ntrolButton)
.Caption = "UP one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoCo ntrolButton)
.Caption = "DOWN one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoCo ntrolButton)
.Caption = "UP by percent"
.OnAction = "Amend_BidAsk_Percent_UP"
End With etc etc..............
I have tried many times to consolidate the menu set up within one With... End with statement but without success. There must be a way to do it right? Any ideas where I am going wrong pls? Many many thanks in advance.

Check out "Menu Routines" section on page:
http://www.bygsoftware.com/examples/examples.htm

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"


"Brownie" wrote in message
...
Greetings.... I have created a right-click menu in Excel as follows:
With Application.CommandBars("Cell").Controls.Add(msoCo ntrolButton)
.Caption = "UP one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoCo ntrolButton)
.Caption = "DOWN one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoCo ntrolButton)
.Caption = "UP by percent"
.OnAction = "Amend_BidAsk_Percent_UP"
End With etc etc..............
I have tried many times to consolidate the menu set up within one With...
End with statement but without success. There must be a way to do it right?
Any ideas where I am going wrong pls? Many many thanks in advance.

With Application.commandBars("Cell")
with .Controls.Add(msoControlButton)
.Caption = "UP one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With .Controls.Add(msoControlButton)
.Caption = "DOWN on tick"
.OnAction = "Amend_BidAsk_Tick_DOWN"
End With
With .Controls.Add(msoControlButton)
.Caption = "UP by percent"
.OnAction = "Amend_BidAsk_Percent_UP"
End With
End With

would be about the best you could do using a With construct since each
button is a separate object.

--
Regards,
Tom Ogilvy


"Brownie" wrote in message
...
Greetings.... I have created a right-click menu in Excel as follows:
With Application.CommandBars("Cell").Controls.Add(msoCo ntrolButton)
.Caption = "UP one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoCo ntrolButton)
.Caption = "DOWN one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoCo ntrolButton)
.Caption = "UP by percent"
.OnAction = "Amend_BidAsk_Percent_UP"
End With etc etc..............
I have tried many times to consolidate the menu set up within one With...
End with statement but without success. There must be a way to do it right?
Any ideas where I am going wrong pls? Many many thanks in advance.

Tom,

Thanks for this. Not as clean as I was expecting but that's fine. Little extra question... is there any way one can have a right click menu that scrolls right to another option (similar to the "Format - Columns - Width" on the menubar) rather than the "..." leading to a dialog box? If so could you insert what code I need into:
With .Controls.Add(msoControlButton)
.Caption = "DOWN on tick"
.OnAction = "Amend_BidAsk_Tick_DOWN"
End With
ie. the user right clicks, scrolls to DOWN on tick which then scrolls right to give 2-3 new options...
Again, huge thanks in advance. Rgds. Brownie.

This worked for me:

Sub BuildMenu()

With Application.CommandBars("Cell")
.Reset
With .Controls.Add(msoControlButton)
.Caption = "UP one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With .Controls.Add(msoControlPopup)
.Caption = "Tick DOWN"
With .Controls.Add(msoControlButton)
.Caption = "DOWN on tick"
.OnAction = "Amend_BidAsk_Tick_DOWN1"
End With
With .Controls.Add(msoControlButton)
.Caption = "DOWN on tick2"
.OnAction = "Amend_BidAsk_Tick_DOWN2"
End With
End With

With .Controls.Add(msoControlButton)
.Caption = "UP by percent"
.OnAction = "Amend_BidAsk_Percent_UP"
End With
End With

End Sub

Sub Amend_BidAsk_Percent_Up()
MsgBox "in Amend_BidAsk_Percent_Up"
End Sub

Sub Amend_BidAsk_Tick_DOWN1()
MsgBox "in Amend_BidAsk_Tick_DOWN1"
End Sub

Sub Amend_BidAsk_Tick_DOWN2()
MsgBox "in Amend_BidAsk_Tick_DOWN2"
End Sub

Sub Amend_BidAsk_Tick_UP()
MsgBox "in Amend_BidAsk_Tick_UP"

End Sub

--
Regards,
Tom Ogilvy

"Brownie" wrote in message
...
Tom,

Thanks for this. Not as clean as I was expecting but that's fine. Little
extra question... is there any way one can have a right click menu that
scrolls right to another option (similar to the "Format - Columns - Width"
on the menubar) rather than the "..." leading to a dialog box? If so could
you insert what code I need into:
With .Controls.Add(msoControlButton)
.Caption = "DOWN on tick"
.OnAction = "Amend_BidAsk_Tick_DOWN"
End With
ie. the user right clicks, scrolls to DOWN on tick which then scrolls
right to give 2-3 new options...
Again, huge thanks in advance. Rgds. Brownie.

How does one remove an entry from the right click menu ?

Thanks

Michael Singmin

================================================== ===========
"Tom Ogilvy" wrote:

With Application.commandBars("Cell")
with .Controls.Add(msoControlButton)
.Caption = "UP one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With .Controls.Add(msoControlButton)
.Caption = "DOWN on tick"
.OnAction = "Amend_BidAsk_Tick_DOWN"
End With
With .Controls.Add(msoControlButton)
.Caption = "UP by percent"
.OnAction = "Amend_BidAsk_Percent_UP"
End With
End With

would be about the best you could do using a With construct since each
button is a separate object.

Application.commandBars("Cell").Controls("caption of control").Delete

You can experiment in the immediate window to find the caption:

? application.CommandBars("Cell").Controls(3).Captio n
&Paste

so to refer to that control

application.CommandBars("Cell").Controls("&Paste") .Caption


? application.CommandBars("Cell").Controls("&Paste") .Caption
&Paste

But captions can be different for different geographical settings, so you
can use the ID to look for the control:

? application.CommandBars("Cell").Controls("&Paste") .Id
22

Now, knowing the ID you can get a reference to the control:

set btn = application.CommandBars("Cell").FindControl(id:=22 )

? btn.Caption
&Paste

so then you could do

btn.Delete
or
application.CommandBars("Cell").FindControl(id:=22 ).Delete

for a custom button, however, you can't use the ID.

--
Regards,
Tom Ogilvy




Michael Singmin wrote in message
...

How does one remove an entry from the right click menu ?

Thanks

Michael Singmin

================================================== ===========
"Tom Ogilvy" wrote:

With Application.commandBars("Cell")
with .Controls.Add(msoControlButton)
.Caption = "UP one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With .Controls.Add(msoControlButton)
.Caption = "DOWN on tick"
.OnAction = "Amend_BidAsk_Tick_DOWN"
End With
With .Controls.Add(msoControlButton)
.Caption = "UP by percent"
.OnAction = "Amend_BidAsk_Percent_UP"
End With
End With

would be about the best you could do using a With construct since each
button is a separate object.

Thanks Tom,

Most instructive answer.

Michael
================================================== ===========
"Tom Ogilvy" wrote:

Application.commandBars("Cell").Controls("capti on of control").Delete

You can experiment in the immediate window to find the caption:

? application.CommandBars("Cell").Controls(3).Captio n
&Paste

so to refer to that control

application.CommandBars("Cell").Controls("&Paste" ).Caption


? application.CommandBars("Cell").Controls("&Paste") .Caption
&Paste

But captions can be different for different geographical settings, so you
can use the ID to look for the control:

? application.CommandBars("Cell").Controls("&Paste") .Id
22

Now, knowing the ID you can get a reference to the control:

set btn = application.CommandBars("Cell").FindControl(id:=22 )

? btn.Caption
&Paste

so then you could do

btn.Delete
or
application.CommandBars("Cell").FindControl(id:=2 2).Delete

for a custom button, however, you can't use the ID.