Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro appears 5 times
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, Befo=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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro appears 5 times
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 "XP" wrote in message ... | 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, Befo=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. | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro appears 5 times
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" wrote: 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 "XP" wrote in message ... | 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, Befo=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. | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro appears 5 times
Cancel that last post; once I took care of the "shotgun" the issue went away.
Thanks Jim! "XP" wrote: 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" wrote: 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 "XP" wrote in message ... | 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, Befo=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. | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy exactly the I number of times that he/she appears in the column | Excel Programming | |||
I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW | Excel Worksheet Functions | |||
How do I count the number of times a value appears? | Excel Worksheet Functions | |||
need formula for % of times text appears in row. | Excel Worksheet Functions | |||
How do I count how many times x appears in a column? | Excel Worksheet Functions |