Tips27 : CommandBar Menu & Add-in Tab with add-in of Excel2013/2016.
      ( Because I who am poor at English am translating into English while using translation software,  
        there may be an odd expression. The mistranslation revises it sequentially. )

  (Jpn. 1st Edition : 6 Dec. 2016 )
  (Eng. Translation: 21 Dec. 2016 )
This is a procedure manual for trouble-free operation of CommandBar menu ( Add-in Tab )
created by Add-in book in SDI ( Single Documents Interface ) environment of Excel2013 / 2016.
Please note that in the case of "Save type menu ( Temporay: = False )", differences in execution
procedure of add-in registration / cancellation cause troubles that are not reflected as expected.
The distinction between [ Temporary: = False or True ] can be judged by whether or not the
add-in tab immediately appears at the time of registering the add-in
. (see Sec.5 / Sec.6)

1.  CommandBar menu and Ribbon UI
2.  How to create a CommandBar menu in Add-in book
3.  MDI  and  SDI
4.  Macro for verification
5.  Temporary type menu ( Temporary: = True ) and Procedure for Add-in registration / cancellation
6.  Save type menu ( Temporary: = False ) and Procedure for Add-in registration / cancellation
    6-1.  Procedure in which Add-in menu is created / deleted as expected
    6-2.  Procedure in which Add-in menu is not created / deleted as expected
7.  Bug ?  or  Specification ?



[ Link to here ]

1. CommandBar menu and Ribbon UI

The menu by CommandBar is used in Excel 2003 and earlier. In Excel 2007 and later, menus
created by CommandBar are displayed in "Add-in" tab (The CommandBar menu created in a
normal book that is not an add-in book is also accommodated in the "add-in" tab.)

There is a temporary display problem with the ribbon display of the menu (accommodated in the
add-in tab) added on CommandBars ("WorkSheet Menu Bar")
.
 
  [ KB 2648925 ]  https://support.microsoft.com/ja-jp/kb/2648925  ( English edition is not released. )
    If you select the Add-Ins tab of the ribbon while the graph object is active,
    commands on [Add-in] - [Menu command] may not be displayed.
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    While the graph object is active (selected state), the menu temporarily disappears.
    If you activate another object such as a sheet, the menu will be displayed again.
    If only the hidden menu is registered in the add-in tab, the add-in tab itself also temporarily disappears.
    It merely temporarily disappears, and the menu itself is not deleted.
    If you create it under the menu of "Tools" etc. (not on "WorkSheet Menu Bar"),
    you can avoid this problem.



[ Link to here ]

2. How to create a CommandBar menu in Add-in book
      ( Note ) In the CommandBar menu, duplicate names can be registered. Therefore, you should make sure "existence of
                  menu name to be created" beforehand (part of 'existence confirmation' is omitted in the following macro).


( 1 ) Temporary type menu ( Temporary := True )
    By writing a macro in Workbook_Open event procedure, it is created every time Excel is started.
    This menu will be deleted automatically at the end of Excel.

    Since it is automatic deletion at the end of Excel, even if you cancel add-ins, the menu will remain
    during that excel process. Therefore, it is necessary to delete the menu in Workbook_AddinUninstall
    or Workbook_BeforeClose event procedure.

   --- ThisWorkbook module ---

 Private Sub Workbook_Open()
   Dim MyCB As CommandBar
   Dim MyCBTool As CommandBarControl
   Dim MyCBMenu As CommandBarControl

   On Error Resume Next

   Set MyCB = Application.CommandBars("WorkSheet Menu Bar")
   Set MyCBMenu = MyCB.Controls.Add(Type:=msoControlPopup, Temporary:=True)
   MyCBMenu.Caption = "Menu1"

   Set MyCBTool = MyCB.FindControl(ID:=30007)    '[Tool (&T)]  ID=30007
   Set MyCBMenu = MyCBTool.Controls.Add(Type:=msoControlPopup, Temporary:=True)
   MyCBMenu.Caption = "Menu2"
 End Sub


 Private Sub Workbook_AddinUninstall()
 ' or Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim MyCB As CommandBar
   Dim MyCBTool As CommandBarControl

   On Error Resume Next

   Set MyCB = Application.CommandBars("WorkSheet Menu Bar")
   MyCB.Controls("Menu1").Delete

   Set MyCBTool = MyCB.FindControl(ID:=30007)    '[Tool (&T)]  ID=30007
   MyCBTool.Controls("Menu2").Delete
 End Sub


( 2 ) Save type menu ( Temporary := False )
    Menus of this type are not automatically deleted.
    You create menus of this type with Workbook_AddinInstall and delete it with Workbook_AddinUninstall.
    In other words, it is executed only when add-in registration / add-in cancellation (check box of add-in
    dialog is turned on / off).

    Menus of this type are recorded as a permanent menu in the following Excel configuration file.
      C:\Users\USER ACCOUNT\AppData\Roaming\Microsoft\Excel\Excel15.xlb( in Windows10 )
          Excel2013/2016 ... Excel15.xlb  ,  Excel2010 ... Excel14.xlb
          Excel2007 ... Excel12.xlb  ,  Excel2003 ... Excel11.xlb


   --- ThisWorkbook module ---

 Private Sub Workbook_AddinInstall()
   Dim MyCB As CommandBar
   Dim MyCBTool As CommandBarControl
   Dim MyCBMenu As CommandBarControl

   On Error Resume Next

   Set MyCB = Application.CommandBars("WorkSheet Menu Bar")
   Set MyCBMenu = MyCB.Controls.Add(Type:=msoControlPopup, Temporary:=False)
   MyCBMenu.Caption = "Menu3"

   Set MyCBTool = MyCB.FindControl(ID:=30007)    '[Tool (&T)]  ID=30007
   Set MyCBMenu = MyCBTool.Controls.Add(Type:=msoControlPopup, Temporary:=False)
   MyCBMenu.Caption = "Menu4"
 End Sub


 Private Sub Workbook_AddinUninstall()
   Dim MyCB As CommandBar
   Dim MyCBTool As CommandBarControl

   On Error Resume Next

   Set MyCB = Application.CommandBars("WorkSheet Menu Bar")
   MyCB.Controls("Menu3").Delete

   Set MyCBTool = MyCB.FindControl(ID:=30007)    '[Tool (&T)]  ID=30007
   MyCBTool.Controls("Menu4").Delete
 End Sub



[ Link to here ]

3. MDI  and  SDI

( 1 )  The window of Excel2007/2010 is MDI (Multi Document Interface).

    The MDI contains multiple child windows that are nested within a single parent window and
    the menu or tool bar is only in the parent window and each book uses a common ribbon UI.

    Therefore, even if you open multiple workbooks or restart Excel, you can always use menu
    commands from the "Add-in" tab.

( 2 )  From Excel2013, the window has changed to SDI (Single Document Interface).

    In SDI, each Excel window can contain only one workbook, and each has its own ribbon UI.
    When you open a new workbook, it will be displayed in another Excel window, even though
    it is the same Excel instance.

    Therefore, when you open multiple workbooks or restart Excel, the "Add-in" tab does not
    appear in the window of each workbook (or the menu command in the add-in tab is not
    displayed) in some cases.

( 3 )  Effect of SDI conversion (Excel 2013 or later) on Add-in Tab.

  ( a ) The CommandBar menu created with the macro of the normal workbook is effective only
        in the window of this workbook. The menu appears in the window of the workbook in Add-in
        tab, but when you switch to another workbook's window, the menu disappears.
        (If there is no other valid menu, Add-in tab itself will also disappear.)

  ( b ) The CommandBar menu created with the macro of the add-in workbook is effective in all
        workbook windows (The method of creating the menu is as described above).
        However, depending on the execution procedure of add-in registration, Add-in tab may
        not appear. This is "Problem on the CommandBar menu in SDI".


[ Link to here ]

4. Macro for verification
    SDI_AddinTab_Test_E.zip ( 54 kb )
      +-- MenuTestOpen.xlam
      +-- MenuTestAddinInst.xlam
      +-- MenuTestCheckDelete.xlsm

( 1 ) MenuTestOpen.xlam
      It is an add-in workbook that creates a Temporary type CommandBar menu (Temporary: = True).
      It creates a menu with Workbook_Open and deletes it with Workbook_BeforeClose.
      ( a ) WkshOpenTempT ..... Menu to create in "WorkSheet Menu Bar"
      ( b ) ToolOpenTempT ..... Menu to be created under "Tools" menu

      A macro to check the menu state ( MenuCheck4Open ) is included.

( 2 ) MenuTestAddinInst.xlam
      It is an add-in workbook that creates a Save type CommandBar menu (Temporary: = False).
      It creates a menu with Workbook_AddinInstall and deletes it with Workbook_AddinUninstall.
      ( a ) WkshAddinTempF ..... Menu to create in "WorkSheet Menu Bar"
      ( b ) ToolAddinTempF ..... Menu to be created under "Tools" menu

      A macro to check the menu state ( MenuCheck4AddinInst ) is included.

( 3 ) MenuTestCheckDelete.xlsm
    It is a macro to check the menu state of both ( 1 ) and ( 2 ).


[ Link to here ]

5. Temporary type menu ( Temporary: = True ) and Procedure for Add-in
    registration / cancellation
    ( This section is targeted for Excel 2013 and later. )
      The distinction between [ Temporary: = False or True ] can be judged by whether or not the add-in tab
      immediately appears at the time of registering the add-in. (see Sec.5 / Sec.6)


This type of add-in menu appears by newly creating by add-in workbook every Excel startup.

Since the add-in workbook is opened prior to the normal workbook at Excel startup, the add-in
menus are also created before the normal workbook is opened. As a result, add-in menus are
enabled for all workbooks that will be opened after that.

In addition, this type of add-in menu has the problem that it is not immediately reflected on
the ribbon (add-in tab) during the Excel process that registered / cancelled the add-in
.

This type of add-in menu is always reflected in all workbook windows after restarting Excel.

[ Temporary := True ]
Case of restarting immediately after add-in registration / cancellation

( 1 ) Start Excel ( Open Book1 )  
( 2 ) Add-in registration in Book1 Menu disappears
( 3 ) Exit Excel in Book1  
( 4 ) Restart Excel ( Open Book1 ) Menu appears
( 5 ) Open Book2 additionally Menu appears in Book2

( 6 ) Start Excel ( Open Book1 ) Menu appears
( 7 ) Add-in cancellation in Book1 Menu remains
( 8 ) Exit Excel in Book1  
( 9 ) Restart Excel ( Open Book1 ) Menu disappears


[ Temporary := True ]
Case of not restarting immediately after add-in registration / cancellation

( 1 ) Start Excel ( Open Book1 )  
( 2 ) Add-in registration in Book1 Menu disappears
( 3 ) Open Book2 additionally Menu disappears in Book2
( 4 ) Close Book1 & Book2 ( Empty Excel window ) Menu appears
( 5 ) Open Book3 additionally Menu appears in Book3
( 6 ) Exit Excel in Book3  
( 7 ) Restart Excel ( Open Book1 ) Menu appears
( 8 ) Open Book2 additionally Menu appears in Book2

( 9 ) Start Excel ( Open Book1 ) Menu appears
( 10 ) Add-in cancellation in Book1 Menu remains
( 11 ) Open Book2 additionally Menu remains in Book2
( 12 ) Close Book1 & Book2 ( Empty Excel window ) Menu remains
( 13 ) Open Book3 additionally Menu remains in Book3
( 14 ) Exit Excel in Book3  
( 15 ) Restart Excel ( Open Book1 ) Menu disappears


[ Link to here ]

6. Save type menu ( Temporary: = False ) and Procedure for Add-in
    registration / cancellation
    ( This section is targeted for Excel 2013 and later. )
      The distinction between [ Temporary: = False or True ] can be judged by whether or not the add-in tab
      immediately appears at the time of registering the add-in. (see Sec.5 / Sec.6)


This type of add-in menu is recorded in the Excel configuration file ( Excel15.xlb ).
By doing this, the add-in menu will appear even the next time Excel starts up.

However, menu information may not be recorded in Excel 15.xlb depending on the
operation procedure for registering / cancelling the add-in
. As a result, the add-in menu
may not be appeared / disappeared as expected.

( Note ) In the following verification, I actually confirmed the update status of Excel15.xlb
            (whether updating is done or not) in the editor.

[ Link to here ]
[ Temporary := False ]  Add-in Registration
  Operating Procedure in which Add-in Menu appears as expected.


-- Register Case 1 --
( It is only this procedure that menu will appear as expected in add-in registration )
( 1 ) Start Excel ( Open Book1 )  
( 2 ) Add-in registration in Book1 Menu appears
( 3 ) Open Book2 additionally ( Required ) Menu appears in Book2
( 4 ) First, close Book1 that add-in was
registered
( Required )
Menu remains in Book2
( 5 ) Next, exit Excel in Book2 that menu
is appearing
( Required )
Menu data is recorded in Excel15.xlb
( 6 ) Restart Excel ( Open Book1 ) Menu appears
( 7 ) Open Book2 additionally Menu appears in Book2
( 8 ) Close Book1 / Book2 ( Empty Excel window ) Menu appears


[ Temporary := False ]  Add-in Cancellation
  Operating Procedure in which Add-in Menu disappears as expected.


-- Cancel Case 1 --
( 1 ) Start Excel ( Open Book1 ) Menu appears
( 2 ) Add-in cancellation in Book1 Menu disappears
( 3 ) Exit Excel in Book1 Menu data is deleted from Excel15.xlb
( 4 ) Restart Excel ( Open Book1 ) Menu disappears
 
-- Cancel Case 2 --
( 1 ) Start Excel ( Open Book1 ) Menu appears
( 2 ) Add-in cancellation in Book1 Menu disappears
( 3 ) Open Book2 additionally Menu disappears in Book2
( 4 ) Close Book1 Menu disappears in Book2
( 5 ) Exit Excel in Book2 Menu data is deleted from Excel15.xlb
( 6 ) Restart Excel ( Open Book1 ) Menu disappears
 
-- Cancel Case 3 --
( 1 ) Start Excel ( Open Book1 ) Menu appears
( 2 ) Open Book2 additionally Menu appears in Book2
( 3 ) Add-in cancellation in Book2 Menu disappears in Book2
/ Menu remains in Book1
( 4 ) First, close Book1 that menu is appearing Menu disappears in Book2
( 5 ) Next, Exit Excel in Book2 Menu data is deleted from Excel15.xlb
( 6 ) Restart Excel ( Open Book1 ) Menu disappears

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
[ Link to here ]
[ Temporary := False ]  Add-in Registration
  Operating Procedure in which Add-in Menu does not appear as expected.


-- Register Case 2 --
( 1 ) Start Excel ( Open Book1 )  
( 2 ) Add-in registration in Book1 Menu appears
( 3 ) Exit Excel in Book1 Menu data is not recorded in Excel15.xlb
( 4 ) Restart Excel ( Open Book1 ) Menu disappears
 
-- Register Case 3 --
( 1 ) Start Excel ( Open Book1 )  
( 2 ) Add-in registration in Book1 Menu appears
( 3 ) Open Book2 additionally Menu appears in Book2
( 4 ) Close Book2 Menu remains in Book1
( 5 ) Exit Excel in Book1 Menu data is not recorded in Excel15.xlb
( 6 ) Restart Excel ( Open Book1 ) Menu disappears
 
-- Register Case 4 --
( 1 ) Start Excel ( Open Book1 )  
( 2 ) Add-in registration in Book1 Menu appears
( 3 ) Close Book1 ( Empty Excel window ) Menu disappears
( 4 ) Open Book2 additionally Menu disappears in Book2
( 5 ) Exit Excel in Book2 Menu data is not recorded in Excel15.xlb
( 6 ) Restart Excel ( Open Book1 ) Menu disappears
 
-- Register Case 5 --
( 1 ) Start Excel ( Open Book1 )  
( 2 ) Add-in registration in Book1 Menu appears
( 3 ) Open Book2 additionally Menu appears in Book2
( 4 ) Close Book1 Menu remains in Book2
( 5 ) Close Book2 ( Empty Excel window ) Menu disappears
( 6 ) Open Book3 additionally Menu disappears in Book3
( 7 ) Exit Excel in Book3 Menu data is not recorded in Excel15.xlb
( 8 ) Restart Excel ( Open Book1 ) Menu disappears
 
-- Register Case 6 --
( 1 ) Start Excel ( Open Book1 )  
( 2 ) Open Book2 additionally  
( 3 ) Add-in registration in Book2 Menu appears in Book2
/ Menu disappears in Book1
( 4 ) Close Book1 Menu remains in Book2
( 5 ) Exit Excel in Book2 Menu data is not recorded in Excel15.xlb
( 6 ) Restart Excel ( Open Book1 ) Menu disappears
 
-- Register Case 7 --
( 1 ) Start Excel ( Open Book1 )  
( 2 ) Open Book2 additionally  
( 3 ) Add-in registration in Book2 Menu appears in Book2
/ Menu disappears in Book1
( 4 ) Close Book2 Menu disappears in Book1
( 5 ) Exit Excel in Book1 Menu data is not recorded in Excel15.xlb
( 6 ) Restart Excel ( Open Book1 ) Menu disappears
 
-- Register Case 8 --
( 1 ) Start Excel ( Open Book1 )  
( 2 ) Open Book2 additionally  
( 3 ) Add-in registration in Book2 Menu appears in Book2
/ Menu disappears in Book1
( 4 ) Open Book3 additionally Menu appears in Book2 & Book3
/ Menu disappears in Book1
( 5 ) Close Book2 Menu remains in Book3
/ Menu disappears in Book1
( 6 ) Exit Excel with Book1 & Book3 closed all at once Menu data is not recorded in Excel15.xlb
( 7 ) Restart Excel ( Open Book1 ) Menu disappears
 
If you leave Book3 to the end, menu data will be recorded ( same as Register:Case 1 )
( 5 ) Close Book2 Menu remains in Book3
/ Menu disappears in Book1
( 6-1 ) Close Book1 Menu remains in Book3
( 6-2 ) Exit Excel in Book3 that menu is appearing Menu data is recorded in Excel15.xlb
( 7 ) Restart Excel ( Open Book1 ) Menu appears


[ Temporary := False ]  Add-in Cancellation
  Operating Procedure in which Add-in Menu does not disappear as expected.


-- Cancel Case 4 --
( 1 ) Start Excel ( Open Book1 ) Menu appears
( 2 ) Open Book2 additionally Menu appears in Book2
( 3 ) Add-in cancellation in Book2 Menu disappears in Book2
/ Menu remains in Book1
( 4 ) Open Book3 additionally Menu disappears in Book2 & Book3
/ Menu remains in Book1
( 5 ) Close Book2 & Book3 Menu remains in Book1
( 6 ) Exit Excel in Book1 Menu data is not deleted from Excel15.xlb
( 7 ) Restart Excel ( Open Book1 ) Menu remains
( The state of Add-in is canceled )
( 8 ) Add-in registration in Book1 Menu is registered with the same
name ( Duplicate )
 
-- Cancel Case 5 --
( 1 ) Start Excel ( Open Book1 ) Menu appears
( 2 ) Open Book2 additionally Menu appears in Book2
( 3 ) Add-in cancellation in Book2 Menu disappears in Book2
/ Menu remains in Book1
( 4 ) Close Book2 Menu remains in Book1
( 5 ) Open Book3 additionally Menu appears in Book1 & Book3
( 6 ) Exit Excel with Book1 & Book3 closed all at once Menu data is not deleted from Excel15.xlb
( 7 ) Restart Excel ( Open Book1 ) Menu remains
( The state of Add-in is canceled )
( 8 ) Add-in registration in Book1 Menu is registered with the same
name ( Duplicate )

( Note )
The problem of "being registered duplicately with the same name" can be avoided
by setting the macro on the add-in side to "check the existence of the menu before
creating the CommandBar menu
".


[ Link to here ]

7. Bug ?  or  Specification ?

The point that it is not reflected on the ribbon immediately after registering the add-in is described
in the following document, so I think that it will be "specification".
      [ Programming for the Single Document Interface in Excel ]
      https://msdn.microsoft.com/en-us/library/office/dn251093.aspx
   
  Solutions for SDI Issues
      Toolbars added to Excel workbooks via command bar code and XLA files
      will not be displayed until after you close and reopen the workbook.


      [ KB2761240 ]
      Command bars of Excel add-ins are not displayed or removed automatically
      in Excel 2013 or later when you load or unload the add-ins
      https://support.microsoft.com/en-us/kb/2761240
   
  Workaround
      To work around issue 1, close all open workbooks, and then restart Excel.



However, I think that the following behavior is a bug.
 
    The menu is not appeared ( or disappeared ) on the add-in tab
    due to "Update content is not recorded" in the Excel configuration
    file ( Excel15.xlb ).

Without this problem, the save type menu ( Temporary := False ) should always be reflected in the
add-in tab by simply restarting Excel after updating the CommandBar menu.

However, even if it is "Bug", there is no possibility that this problem will be fixed (CommandBar
is already treated as "old function"). To users using add-ins, please instruct them to perform
add-in registration in the above procedure.




[ AddinBox Home (Japanese) ]  [ English Home ]  


AddinBox ( K.Tsunoda in Japan ) CopyRight(C) 2016 Allrights Reserved.