Macro appears 5 times

X

XP

Aside: Is there a separate NG for XL 2007? --- If not maybe there should be?

In XL 2007, I add a program that adds a custom menu item to the shortcut menu
and it works fine. BUT, two things:

1) A menu item in the ribbon called "Add-Ins" appears. How can I delete or
hide this or otherwise prevent it from appearing?

2) In the "Add-Ins" item, the custom shortcut menu item appears 5 times!
Why? [I know this is sort of moot since I want to get rid of "Add-Ins"
anyway, but I'm curious why this happens - at some point I'll probably need
to fix it.]

My code that adds the custom shortcut menu:

In ThisWorkbook module:

Private Sub Workbook_Activate()
Call ShortCutMenuModify
End Sub

Private Sub Workbook_Deactivate()
Call ShortCutMenuReset
End Sub

In a standard code module:

Public Function ShortCutMenuModify()
Dim cbBar As CommandBar
Dim lX As Long
On Error Resume Next
For lX = 1 To Application.CommandBars.Count
If CommandBars(lX).Type = msoBarTypePopup And CommandBars(lX).BuiltIn =
True Then
Set cbBar = Application.CommandBars(lX)
With cbBar
.Controls.Add Type:=msoControlButton, Before:=1
.Controls(1).Caption = "Custom"
.Controls(1).FaceId = 5828
.Controls(1).OnAction = "RunCustom"
End With
End If
Next lX
On Error GoTo 0
End Function

Public Function ShortCutMenuReset()
Dim cmdBar As CommandBar
Dim lngX As Long
For lngX = 1 To Application.CommandBars.Count
If CommandBars(lngX).Type = msoBarTypePopup And
CommandBars(lngX).BuiltIn = True Then CommandBars(lngX).Reset
Next lngX
End Function

Thanks for any help on this.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top