Displaying A custom menu as a popup menu

[Guest]

I've created custom a custom menu with various macros, how can I display this
as a popup (shortcut) menu when a cell is right clicked?

 

[JW]

In the sheet module where you want the right_click menu to appear,
place something like this. You can set a For..Next loop to add as
many buttons as you may need.
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)
Dim cMain As CommandBarControl, btn As CommandBarControl
For Each cMain In Application.CommandBars("Cell").Controls
If cMain.Caption = "Your Caption Here" Then cMain.Delete
Next
Set cMain = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlPopup)
cMain.Caption = "Your Caption Here"
Set btn = cMain.Controls.Add
btn.Caption = "Whatver You Want"
btn.OnAction = "SomeMacro"
Set cMain = Nothing
Set btn = Nothing
End Sub

 

[Guest]

Thanks but I have already created a toolbar manually, how can this become a
popup menu. In access you change the menu type manually to popup and the
assign the name of the toolbar in a form property, I do't want to create a
new tool bar from scratch.

 

[Sandro]

hi,

I've found this code from excel vba help:

This example adds a new menu item to the shortcut menu for cells
B1:B10.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Dim icbc As Object
For Each icbc In Application.CommandBars("cell").Controls
If icbc.Tag = "brccm" Then icbc.Delete
Next icbc
If Not Application.Intersect(Target, Range("b1:b10")) _
Is Nothing Then
With Application.CommandBars("cell").Controls _
.Add(Type:=msoControlButton, before:=6, _
temporary:=True)
.Caption = "New Context Menu Item"
.OnAction = "MyMacro"
.Tag = "brccm"
End With
End If
End Sub

 

[Sandro]

then maybe you can do it this way

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)

Cancel = True
YourToolBar.show

End Sub

 

[Guest]

Hi,

Worked fine with this code:

'Place code below in the standard module:
Sub popupcmb()
Dim myBar As CommandBar
Dim myBarc As CommandBarButton
Dim myBarcb As CommandBarComboBox
Set myBar = CommandBars.Add(Name:="custom", _
Position:=msoBarPopup, _
Temporary:=False)
Set myBarc = myBar.Controls.Add(Type:=msoControlButton, ID:=1)
With myBarc
.Caption = "Test Bar name"
.OnAction = "Procedure1"
End With
myBar.ShowPopup
myBar.Delete
End Sub

Sub Procedure1()
MsgBox "test"
End Sub


'Place code below in the Worksheet module:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
popupcmb
End Sub