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?

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

diddy_david wrote:
> 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?

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.

"JW" wrote:

> 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
>
> diddy_david wrote:
> > 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?
>
>

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


On Sep 21, 10:34 pm, diddy_david
<diddyda...@discussions.microsoft.com> wrote:
> 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?

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

On Sep 21, 10:58 pm, diddy_david
<diddyda...@discussions.microsoft.com> wrote:
> 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.

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

--
Regards,

Halim



"diddy_david" wrote:

> 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?