Van

No provision made for deleting the item when switching workbooks.

Try this amended.

Private Sub Workbook_Activate()
With Application.CommandBars("Cell").Controls.Add(tempo rary:=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

On Fri, 11 Mar 2005 16:04:35 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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(tempo rary:=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(tempo rary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub


Gord Dibben Excel MVP
On Fri, 11 Mar 2005 14:03:06 -0800, "VanS"
wrote:

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