Customizing popup menus

[Guest]

Hello,
I have an Excel/Word VBA app and I need to be able to customize the popup
(right click) menu on an Excel tab so I can add a custom command to insert my
own custom worksheet. I can only find ways to alter the regular menu bar, but
not the popup.
Can anyone tell me how to do so linked to an item to perform a macro or sub
routine. Is there a place to edit such menus or does it need to be done with
code, and if the latter, how?
Thanks, God bless
Van

 

[Bob Phillips]

Van,

Here is some code to add to the menu.

Sub CreateRightClick()
With Application.CommandBars("Cell"­)
With .Controls.Add
.Caption = "Remove"
.OnAction = "Remove"
End With
With .Controls.Add
.Caption = "Remove2"
.OnAction = "Remove2"
End With

End With
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Gord Dibben]

Van

Must be done through code.

Example to add a menu item to right-click which runs a macro.

Sub Workbook_Open()

With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub

Good idea to make sure you clear the item if already on the right-click menu
before adding it or you will get duplicates.

Do this by adding a delete line.

Revised code will look like this.

Sub Workbook_Open() 'or _Activate
Application.CommandBars("Cell").Controls("Clear Formats").Delete
With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub


Gord Dibben Excel MVP

 

[Gord Dibben]

Van

No provision made for deleting the item when switching workbooks.

Try this amended.

Private Sub Workbook_Activate()
With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Cell").Controls("Clear Formats").Delete
End Sub


Gord

 

[Guest]

Thanks Bob. Will check it out.
God bless
Van

Van,

Here is some code to add to the menu.

Sub CreateRightClick()
With Application.CommandBars("Cell"­)
With .Controls.Add
.Caption = "Remove"
.OnAction = "Remove"
End With
With .Controls.Add
.Caption = "Remove2"
.OnAction = "Remove2"
End With

End With
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Gord,
Many thanks for your help. Your suggestions are quite helpful.
God bless
Van