Gord,
Many thanks for your help. Your suggestions are quite helpful.
God bless
Van
"Gord Dibben" wrote:
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
|