CommandBar("Cell") additions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings.... I have created a right-click menu in Excel as follows:
With Application.CommandBars("Cell").Controls.Add(msoControlButton)
.Caption = "UP one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoControlButton)
.Caption = "DOWN one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoControlButton)
.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 said:
Greetings.... I have created a right-click menu in Excel as follows:
With Application.CommandBars("Cell").Controls.Add(msoControlButton)
.Caption = "UP one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoControlButton)
.Caption = "DOWN one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoControlButton)
.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...
Click to expand...
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 said:
Greetings.... I have created a right-click menu in Excel as follows:
With Application.CommandBars("Cell").Controls.Add(msoControlButton)
.Caption = "UP one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoControlButton)
.Caption = "DOWN one tick"
.OnAction = "Amend_BidAsk_Tick_UP"
End With
With Application.CommandBars("Cell").Controls.Add(msoControlButton)
.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...
Click to expand...
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 said:
Tom,

Thanks for this. Not as clean as I was expecting but that's fine. Little
Click to expand...
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"
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
Click to expand...
right to give 2-3 new options...
 
How does one remove an entry from the right click menu ?

Thanks

Michael Singmin

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

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

? application.CommandBars("Cell").Controls(3).Caption
&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.
 
Thanks Tom,

Most instructive answer.

Michael
=============================================================
 
Back
Top