Was this ever solved by someone else? Sorry if this is redundant but I did not see it presented.
The user does not "create" the tab then add controls to it, the Add-Ins tab will only show if
(1) it is enabled in the user's Customized Ribbon Options AND
(2) A control has been added to it, for some add-in, at one time (and was not temporary / removed since).
I think a risk might exist if on the ThisWorkbook Open event of the add-in, you don't remove the control(s). That will leave the commandbarcontrol in the Add-Ins menu, whereas the functionality it invokes is not viewable from the VBE without running it - not cool, in my opinion. (Choosing the menu item would cause the add-in to open, but the user might click no to running macros.
I suggest putting code to remove the menus for any add-in being closed in the Workbook_BeforeClose event *in addition* to making the menu items temporary while being created. If there were a Workbook_Close event, of course I would suggest using it ... but no such event exists. The closest is the Workbook_BeforeClose event. I don't think that the rish an add-in, which usually is not written to by the user, will be reached without the add-in going all the way to being closed. But it is worth taking into account the possibility that it stays open, with no Menu items in view.
You can also remove the controls in the Workbook_AddinUninstall event, but this should be in addition to the Workbook_BeforeClose event of the add-in, because closing an add-in does not uninstall it.
The code to add, you probably already have, but here is an example, on Add-in Open
Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Environment Monitoring").Delete
On Error GoTo 0
With Application.CommandBars("Worksheet Menu Bar")
Set cbcObj = .Controls.Add(Type:=msoControlPopup, Before:=.Controls("Help").Index, Temporary:=True)
cbcObj.Caption = "&Environment Monitoring"
Set cbcObj = _
cbcObj.Controls.Add(Type:=msoControlButton, _
Temporary:=True)
cbcObj.Caption = "&Launch Monitoring Form"
cbcObj.OnAction = "'Module1.CommandBarMacro ""Some Param As Text"" '"
End With
End Sub
In the Add-in's Module1, this sub (assuming I have a userform named frmMonitorWorkbooks to show:
Public Sub CommandBarMacro(sArg As String)
MsgBox sArg
frmMonitorWorkbooks.Show
End Sub
Bookmarks