I’m creating several add-ins that will all use the same top-level menu item. The add-ins will be installed as needed. So someone may have just one and someone else may have half a dozen. The right answer, I think, is to have one controlling add-in that handles the menus (and not much else), but I’ve already started down another path, so I’m going to see where that leads me.
To identify which add-ins use my top-level menu, I use a CustomDocumentProperty. I can then create a function to determine if any other add-ins are loaded.
Dim bReturn As Boolean
Dim ai As AddIn
bReturn = False
For Each ai In Application.AddIns
If ai.Name <> ThisWorkbook.Name Then
If ai.Installed Then
On Error Resume Next
bReturn = Workbooks(ai.Name).CustomDocumentProperties(gsCDPAPP).Value
On Error GoTo 0
If bReturn Then Exit For
AISAppExists = bReturn
In my CreateToolbar procedure:
Set cButtonMain = Application.CommandBars(1).Controls.Add(msoControlPopup, , , FindHelp)
cButtonMain.Caption = gsCBMAIN
Set cButtonMain = Application.CommandBars.FindControl(, , gsCBTAG1)
That gives me a cButtonMain object under which I can build my menu. If a sister add-in exists, then go get the top level menu item. Otherwise, create a new one.
I use two global constants for tagging the controls, gsCBTAG1 and gsCBTAG2. gsCBTAG1 is the same for all related add-ins and is only put on the top level menu item. gsCBTAG2 is different for all related add-ins and is used to tag controls one level below the top level menu item.
Set ctl = Application.CommandBars.FindControl(, , gsCBTAG1)
If Not ctl Is Nothing Then
Set ctls = Application.CommandBars.FindControls(, , gsCBTAG2)
If Not ctls Is Nothing Then
For i = 1 To ctls.Count
If this is the last app, then delete the top level menu item and be done with it. If there is still a related add-in open, then delete all of the second level menu items for this add-in. I don’t tag menu items lower than that because deleting a higher level automatically deletes the lower level.
So the first add-in creates the top level menu item and all subsequent add-ins use it. The last add-in to be unloaded deletes the top level menu item and all previous ones just deleted their portion. It seems to work, but your comments are welcome.
One problem is code duplication. I have very similar code in my toolbar modules in all of the add-ins. A change to any of that code means changing it in several places.