Custom context menu items appearing more than once

[sparky2205]

Hi folks,
I have 2 workbooks, each with the same macros, that I have added to the context menu in each.
Everything works fine as long as I have only one workbook open at a time.
If I have both workbooks open simultaneously, the context menu item replicates in the following manner.
Open wb1: context menu ok
With wb1 open, open wb2: wb1 context menu item ok; wb2 context menu item duplicate
With wb2 still open, close and reopen wb1: wb1 and wb2 context item now duplicate
From this point, once I leave one wb open and close and reopen the other a new replicate will appear in the context menu of the reopened wb.
So I could end up with countless replicates of the context menu item.
If I close both wbs and reopen one it will be back to normal i.e. one context menu entry.

Any way I can avoid this?

 

[RoryA]

How did you customise the context menus?

 

[sparky2205]

In ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Removes the added menu when the workbook is closed.
    Application.CommandBars("Cell").Reset
End Sub

Private Sub Workbook_Open()
Dim MyMenu As Object
Set MyMenu = Application.ShortcutMenus(xlWorksheetCell) _
    .MenuItems.AddMenu("Run Macros", 1)
     
    With MyMenu.MenuItems
    '.Add "NameWantInMenu", "MacroName", ,OrderNumber, , ""
     .Add "Update Worksheet Names", "GetWorkSheetName", , 1, , ""
     .Add "Unhide ALL Worksheets", "RestoreSheet", , 2, , ""
     .Add "Copy a Worksheet", "CopySheet", , 3, , ""
     .Add "Rename a Worksheet", "RenameSheet", , 4, , ""
     .Add "Delete a Worksheet", "Delete_Worksheet", , 5, , ""
     .Add "Print", "PreparePrint", , 6, , ""
    End With
Set MyMenu = Nothing
End Sub

 

[RoryA]

That would be why then - your code doesn't check to see if the menu items already exist. If the code is the same between workbooks, you should probably look at using an add-in. You might also use CustomUI rather than VBA, which should make the customisations workbook specific.

 

[sparky2205]

Any pointers on how I might achieve that Rory.
I'm not a total newbie to vba but I'm not as proficient as guys like yourself.
I haven't worked with CustomUI so I don't know how I'd go about that.
Is there a simple addition to my code that could check if the menu item already exists?
I've tried simply adding:

Set MyMenu = Nothing

before the Set MyMenu... piece.
I thought this would delete the custom menu entry and then recreate it.
But it appears to prevent the menu appearing on the second workbook.

 

[RoryA]

You don't do it in VBA, you edit the CustomUI part of the workbook's structure. I would have pointed you to Ron de Bruin's excellent site on the subject but he no longer has his Windows sections up.

You might try adding:

On error resume next
Application.ShortcutMenus(xlWorksheetCell).MenuItems("Run Macros").Delete
on error goto 0

to the start of the code. I don't see how your Set MyMenu = Nothing could have achieved anything at all, and certainly not affected the other workbook.

 

[sparky2205]

I'm not sure what the Set MyMenu = Nothing did. Just something I was trying to fix the issue.
But it was a bit strange, because it stopped me accessing any of the objects for the workbook in the vbe.

Regardless, your code above has sorted my issue and I am most grateful.
Thank you and enjoy your weekend.