Macro appears 5 times

  • Thread starter Thread starter XP
  • Start date Start date
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.
 
If a commandbar does not exist in Excel 2007 or Excel deigns not to put your
button on a commandbar, it puts it on the Add-ins tab. Since you're
splatting your button on all popups menus some have wound up there. You
know they are all the same button and therefore redundant but Excel just
does what you tell it.

I'd suggest not using a shotgun approach but rather target specific popup
menus.

--
Jim
| 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.
|
 
Gotcha. The reason for the shotgun approach was that I found if a user had a
query on the sheet, then the custom menu wasn't available; or if the user was
in a pivot table, the the custom menu wasn't available. So I just set it to
modify everything which worked great in the past, but obviously, I'll need to
change the logic.

What about the "Add-Ins" item on the ribbon, know of any way to make that go
away?
 
Cancel that last post; once I took care of the "shotgun" the issue went away.

Thanks Jim!


XP said:
Gotcha. The reason for the shotgun approach was that I found if a user had a
query on the sheet, then the custom menu wasn't available; or if the user was
in a pivot table, the the custom menu wasn't available. So I just set it to
modify everything which worked great in the past, but obviously, I'll need to
change the logic.

What about the "Add-Ins" item on the ribbon, know of any way to make that go
away?

Jim Rech said:
If a commandbar does not exist in Excel 2007 or Excel deigns not to put your
button on a commandbar, it puts it on the Add-ins tab. Since you're
splatting your button on all popups menus some have wound up there. You
know they are all the same button and therefore redundant but Excel just
does what you tell it.

I'd suggest not using a shotgun approach but rather target specific popup
menus.

--
Jim
| 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.
|
Click to expand...
Click to expand...
 
Back
Top